From: | Christoph Haller <ch(at)rodos(dot)fzk(dot)de> |
---|---|
To: | SBrett(at)e-mis(dot)com (Steve Brett) |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: list of tables ? |
Date: | 2002-07-12 14:16:52 |
Message-ID: | 200207121216.OAA09315@rodos |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
>
> can anyone point me in the right direction ?
>
> i need to list all the tables in a database.
>
Steve,
Your request reminds me of a similar problem I had.
Try the following:
CREATE VIEW sesql_usertables AS
SELECT
UPPER(u.usename) AS tbl_owner, UPPER(c.relname) AS tbl_name,
UPPER(a.attname) AS col_name, a.atttypid AS col_type,
INT4LARGER(a.attlen, a.atttypmod - 4) AS col_length,
CASE WHEN a.attnotnull=TRUE THEN 0 ELSE 1 END AS col_null, a.attnum AS col_seq,
CASE WHEN EXISTS(SELECT adsrc FROM pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum) THEN
1
ELSE
0
END AS COL_DEFAULT
FROM pg_attribute a,
pg_class c LEFT JOIN pg_user u ON (u.usesysid = c.relowner)
WHERE c.oid = a.attrelid AND NOT (c.relname ~* 'pg_') AND
c.relkind = 'r' AND a.attnum > 0 ;
SELECT * FROM sesql_usertables ORDER BY tbl_owner, tbl_name, col_seq ;
It should give at least some ideas how to retrieve information
from all the tables in a database.
Regards, Christoph
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Luc Lachance | 2002-07-12 14:28:17 | Re: SQL problem with aggregate functions. |
Previous Message | Stephan Szabo | 2002-07-12 14:03:28 | Re: how to inherits the references... |