Re: SQL statement : list table details
От | Oliver Elphick |
---|---|
Тема | Re: SQL statement : list table details |
Дата | |
Msg-id | 1015281052.1405.64.camel@linda обсуждение исходный текст |
Ответ на | SQL statement : list table details ("Dino Hoboloney" <whatever@whatever.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: