Re: list all columns in db

Thank you Jon -- thats the exact sort of trick I was hoping for.

Cheers!

On Jun 7, 2007, at 6:36 PM, Jon Sime 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
>
> 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/

// Jonathan Vanasco

| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| CEO/Founder SyndiClick Networks
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| RoadSound.com - Tools For Bands, Stuff For Fans
| Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -