Thread: SQL statement : list table details

    I am looking for a SQL statement which would list table names, columns,
and column types of a specific DB. So far I have managed to find a statement
that lists all of the tables and columns in the DB with

SELECT a.attnum, a.attname AS field, c.relname AS table_name FROM pg_class
c, pg_attribute a WHERE c.relname NOT LIKE 'pg%' AND relkind = 'r' AND
a.attnum > 0 AND a.attrelid = c.oid ORDER BY table_name, attnum;

unfortunately I am unable to come up with a solution to listing the data
types for the columns listed. Any ideas?

On Thu, 2002-02-28 at 18:57, Dino Hoboloney wrote:
>     I am looking for a SQL statement which would list table names, columns,
> and column types of a specific DB. So far I have managed to find a statement
> that lists all of the tables and columns in the DB with
>
> SELECT a.attnum, a.attname AS field, c.relname AS table_name FROM pg_class
> c, pg_attribute a WHERE c.relname NOT LIKE 'pg%' AND relkind = 'r' AND
> a.attnum > 0 AND a.attrelid = c.oid ORDER BY table_name, attnum;
>
> unfortunately I am unable to come up with a solution to listing the data
> types for the columns listed. Any ideas?

SELECT a.attnum, a.attname AS field, t.typname AS type, c.relname AS
table_name FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname
NOT LIKE 'pg%' AND relkind = 'r' AND a.attnum > 0 AND a.attrelid = c.oid
AND a.atttypid = t.oid ORDER BY table_name, attnum;

If you want refinements like the number of characters in a varchar, or
the precsion and scale of a numeric, you need to do things with
a.atttypmod which I can't currently remember.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "Give, and it will be given to you. A good measure,
      pressed down, taken together and running over,
      will be poured into your lap. For with the same
      measure that you use, it will be measured to
      you."         Luke 6:38

think I answered my own question ....

SELECT a.attnum, a.attname AS field, a.atttypid AS column_type, a.atttypmod
AS type_specific, c.relname AS table_name FROM pg_class c, pg_attribute a
WHERE c.relname NOT LIKE 'pg%' AND relkind = 'r' AND a.attnum > 0 AND
a.attrelid = c.oid ORDER BY table_name, attnum;

this will now list column_type, and type_specific which is type-specific
data such as varchar length.