Re: Getting Table Names in a Particular Database

I understand, So there is no way to fetch table in a single query. The
only way is :

1. Connect demo
2. Execute the query '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'
ORDER BY 1,2;

As in Mysql we can view all tables in a test database from below command :

select table_name from information_schema.tables where table_schema
='test';;

Thanks

Scott Marlowe wrote:
> 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.
>