Обсуждение: list all columns in db

Does anyone have a trick to list all columns in a db ?

I need to audit a few dbs to make sure column & table names are
adhering to our standard semantic syntax.

i figure there has to be an old pg-admin  trick out there to display
a db like

    %(tname)s . %(cname)

or some similar format



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

Jonathan Vanasco ha escrito:
> Does anyone have a trick to list all columns in a db ?

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

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/

On Thu, Jun 07, 2007 at 06:36:07PM -0400, Jon Sime wrote:
>     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

Don't forget "and not a.attisdropped" else you might get something
like

 table_schema | table_name |         column_name
--------------+------------+------------------------------
 public       | foo        | ........pg.dropped.2........
 public       | foo        | col1
 public       | foo        | col3
(3 rows)

--
Michael Fuhr

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

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