From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200207121216.OAA09315@rodos>
Subject: Re: list of tables ?
To: SBrett@e-mis.com (Steve Brett)
Date: Fri, 12 Jul 2002 14:16:52 METDST
Cc: pgsql-sql@postgresql.org
In-Reply-To: 
 <C05E7DA1218ED411BF8A00105AC95A8E05B0F6C5@sv-cntrmail.emis.local>;
	from "Steve Brett" at Jul 11, 102 4:02 pm

> 
> 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