Search Postgresql Archives

Re: list all columns in db

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux