>
> 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_attrdefdWHERE d.adrelid = a.attrelid AND d.adnum = a.attnum) THEN1ELSE0END 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
