Re: list all columns in db

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(at)gmail(dot)com> wrote:
>
>
>
> On 07/06/07, Jon Sime <jsime(at)mediamatters(dot)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
> >
>
>