Wednesday, May 25, 2011

Displaying List of Tables in PostgreSQL

1. Simple List of Tables (in 'public' schema)
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';
The result:
table_name 
------------
test
table1
(2 rows)

2. Display List of Tables with Owners (in 'public' schema)
SELECT c.relname AS table_name, u.usename AS owner
FROM pg_class c, pg_user u, pg_namespace n
WHERE c.relowner = u.usesysid 
AND c.relkind = 'r' 
AND c.relnamespace=n.oid
AND n.nspname = 'public';
The result:
table_name |   owner 
-----------+-----------
test       | socrateos
table1     | socrateos 
(2 rows)

3. Display List of Tables from All Schemata (except system's ones)
SELECT c.relname AS table_name, u.usename AS owner, n.nspname AS schema
FROM pg_class c, pg_user u, pg_namespace n
WHERE c.relowner = u.usesysid 
AND c.relkind = 'r' 
AND c.relnamespace=n.oid
AND n.nspname != 'information_schema' AND n.nspname !='pg_catalog';
The result:
table_name |   owner   | schema 
-----------+-----------+--------
test       | socrateos | public
table1     | socrateos | public
(2 rows)

No comments:

Post a Comment