Re: SQL Command - To List Tables ?

How about
  SELECT * FROM pg_tables;

optionally add:
  WHERE schemaname != 'pg_catalog'
  AND schemaname != 'information_schema'

Willy-Bas Loos


>If you start psql with the -E option you can see the internal commands sent to
>the backend.  This can often give you a lot of hints as to the best way to
>pull catalog data from a db:
>
>jason=# \dt
>********* 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",
>  u.usename as "Owner"
>FROM pg_catalog.pg_class c
>     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
>     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
>WHERE c.relkind IN ('r','')
>      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
>      AND pg_catalog.pg_table_is_visible(c.oid)
>ORDER BY 1,2;
>**************************
>
>So from this to get a list of tables you would execute the following in SQL:
>
>select c.relname FROM pg_catalog.pg_class c
>LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
>WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
>AND pg_catalog.pg_table_is_visible(c.oid);
>
>This will give you tables only.  If you wanted schema's and owners then you
>would execute a similar variant to that thrown out by psql.
>
>Rgds,
>
>Jason
>
>On Wed, 27 Aug 2003 08:37 pm, Peter Moscatt wrote:
>> Is there a SQL command I can issue which will list all the TABLES within
>> a database ?
>>
>> Pete
>>
>>
>>
>