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_c lass c
LEFT JOIN pg_catalog.pg_u ser u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_n amespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_t able_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_c lass c
LEFT JOIN pg_catalog.pg_n amespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_t able_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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
