Re: SQL Command - To List Tables ?

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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster