Re: SQL Command - To List Tables ?

On Wednesday, August 27, 2003, at 07:57 AM, Robby Russell wrote:

> Peter Moscatt wrote:
>
>> Is there a SQL command I can issue which will list all the TABLES
>> within
>> a database ?
>>
>> Pete
>>
>
> Well, when you are the postgres commandline client, you can type
> # \dt
>
> That will show all the tables in your current database.
>

If you would actually like to issue a command, you can start psql with
the -E option. So it will show you all the queries it uses:
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;

Which of course you can pare down to get just the information you want.

Jeff