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 <peterachilds@gmail.com> wrote:
On 07/06/07, Jon Sime <jsime@mediamatters.org > 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