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@cae.wisc.edu]
Sent: Thursday, February 28, 2002 4:59 PM
To: pgsql-general@postgresql.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@postgresql.org so that your
message can get through to the mailing list cleanly