Re: Getting Table Names in a Particular Database

Below is the output of the \d command

SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

I want to specify the database name & fetch tables from that but I think
this query filters from schema 'public';

Thanks

Scott Marlowe wrote:
> On Tue, Aug 30, 2011 at 11:26 PM, Adarsh Sharma
> <adarsh(dot)sharma(at)orkash(dot)com> wrote:
>
>> Dear all,
>>
>> Today I am researching about fetching all the table names in a particular
>> database.
>> There is \dt command but I need to fetch it from metadata.
>> I find some commands as below :
>>
>> 1. SELECT table_name FROM information_schema.tables WHERE table_schema =
>> 'public';
>>
>> 2. SELECT tablename FROM pg_tables WHERE tablename NOT LIKE ‘pg%’ AND
>> tablename NOT LIKE ‘sql%’.
>>
>> But I need to specify a particular database & then fetch tables in that.
>>
>
> Try this, start psql with the -E switch, then run \d and copy and edit
> the query(s) that gives you.
>