Re: Getting Table Names in a Particular Database

[Adarsh Sharma <adarsh.sharma@xxxxxxxxxx>]

 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.marlowe@xxxxxxxxx> wrote:
  

On Tue, Aug 30, 2011 at 11:38 PM, Adarsh Sharma
<adarsh.sharma@xxxxxxxxxx> 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.