From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> |
Cc: | pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Getting Table Names in a Particular Database |
Date: | 2011-08-31 05:44:30 |
Message-ID: | CAOR=d=2EGATAoL+tJkJbuKObj1VasQAYT1OJzgSQb+yXoDh3SA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Aug 30, 2011 at 11:42 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Tue, Aug 30, 2011 at 11:38 PM, Adarsh Sharma
> <adarsh(dot)sharma(at)orkash(dot)com> wrote:
>> 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';
>
> You HAVE to connect to the db you want to query about tables. They
> are isolated from each other.
Also take out the AND pg_catalog.pg_table_is_visible(c.oid) bit if you
want to view all tables in all schemas, not just the ones in your
search path.
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2011-08-31 05:48:35 | Re: Getting Table Names in a Particular Database |
Previous Message | Scott Marlowe | 2011-08-31 05:42:48 | Re: Getting Table Names in a Particular Database |