Re: SQL statement : list table details

On Thu, 28 Feb 2002, Samik Raychauhduri wrote:

> Hi,
> Thanks for this query BTW, I was looking for a query like this :)
> Let's see if anybody comes up with any solution to the data types.
> -samik
>
> 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?

If you're running 7.2, I think format_type(a.atttypid, a.atttypmod) in
the select list will give you the human readable type.