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
>>
>>
>>
>
