From: Christoph Haller <ch@rodos.fzk.de>
To: SBrett@e-mis.com
Cc: pgsql-sql@postgresql.org
Subject: Re: list of tables ?
Date: Fri, 12 Jul 2002 14:16:52 METDST
Message-ID: <200207121216.OAA09315@rodos> (raw)
In-Reply-To: <C05E7DA1218ED411BF8A00105AC95A8E05B0F6C5@sv-cntrmail.emis.local>
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, SBrett@e-mis.com
Subject: Re: list of tables ?
In-Reply-To: <200207121216.OAA09315@rodos>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox