In this query: select n.nspname as table_schema, c.relname as table_name, a.attname as column_name from pg_catalog.pg_attribute a join pg_catalog.pg_class c on ( a.attrelid = c.oid) join pg_catalog.pg_namespace n on (c.relnamespace = n.oid) where c.relkind in ('r','v') and a.attnum > 0 and n.nspname not in ('pg_catalog','information_schema') order by 1,2,3
change the following: where c.relkind in ('r','v') to where c.relkind = 'r' Cheers, Andy On 08/06/07, Peter Childs <[EMAIL PROTECTED]> wrote:
On 07/06/07, Jon Sime <[EMAIL PROTECTED]> wrote: > > Jonathan Vanasco wrote: > > > > Does anyone have a trick to list all columns in a db ? > > No trickery, just exploit the availability of the SQL standard > information_schema views: > > select table_schema, table_name, column_name > from information_schema.columns > where table_schema not in ('pg_catalog','information_schema') > order by 1,2,3 Is there any easy way to remove the views from the query? Peter. If you want an equivalent that uses pg_catalog (non-portable outside of > PostgreSQL) you could instead do: > > select n.nspname as table_schema, c.relname as table_name, > a.attname as column_name > from pg_catalog.pg_attribute a > join pg_catalog.pg_class c on ( a.attrelid = c.oid) > join pg_catalog.pg_namespace n on (c.relnamespace = n.oid) > where c.relkind in ('r','v') and a.attnum > 0 > and n.nspname not in ('pg_catalog','information_schema') > order by 1,2,3 > > -Jon > > -- > Senior Systems Developer > Media Matters for America > http://mediamatters.org/ > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: don't forget to increase your free space map settings >