Re: SQL statement : list table details

Here are a few that may help you:

Getting database names:
SELECT datname AS database
FROM pg_database;

Getting table names for the current database:
SELECT tablename
FROM pg_tables
WHERE tablename !~* 'pg_*'

Getting the columns for a table ($table is tablename from above):
SELECT a.attnum, a.attname AS field, t.typname AS type,
a.attlen AS length, a.atttypmod AS length_var,
a.attnotnull AS not_null, a.atthasdef as has_default
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = '$table'
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
ORDER BY a.attnum;

Get default value for a given field ($table is from 2 above, and $field is
from attnum above):
SELECT a.adsrc AS field_default
FROM pg_attrdef a, pg_class c
WHERE c.relname = '$table'
AND c.oid = a.adrelid
AND a.adnum = $field;

I hope this helps a bit.

Rob

-----Original Message-----
From: Samik Raychauhduri [mailto:samik(at)cae(dot)wisc(dot)edu]
Sent: Thursday, February 28, 2002 4:59 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] SQL statement : list table details

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly