Lists: | pgsql-general |
---|
From: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
---|---|
To: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | list all columns in db |
Date: | 2007-06-07 21:34:29 |
Message-ID: | 43A02BFC-953B-49BC-AEF2-A1956CE07287@2xlp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
From: | Rodrigo De León <rdeleonp(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: list all columns in db |
Date: | 2007-06-07 22:34:26 |
Message-ID: | 1181255666.295148.167400@p47g2000hsd.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Jonathan Vanasco ha escrito:
> Does anyone have a trick to list all columns in a db ?
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
From: | Jon Sime <jsime(at)mediamatters(dot)org> |
---|---|
To: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
Cc: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: list all columns in db |
Date: | 2007-06-07 22:36:07 |
Message-ID: | 46688857.30003@mediamatters.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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/
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Jon Sime <jsime(at)mediamatters(dot)org> |
Cc: | Jonathan Vanasco <postgres(at)2xlp(dot)com>, pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: list all columns in db |
Date: | 2007-06-08 02:24:47 |
Message-ID: | 20070608022447.GA2575@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | "Peter Childs" <peterachilds(at)gmail(dot)com> |
---|---|
To: | |
Cc: | "pgsql general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: list all columns in db |
Date: | 2007-06-08 07:21:19 |
Message-ID: | a2de01dd0706080021q4f0746e5h59ed95d32da7a5e2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
>
From: | "Andy Dale" <andy(dot)dale(at)gmail(dot)com> |
---|---|
To: | "Peter Childs" <peterachilds(at)gmail(dot)com> |
Cc: | "pgsql general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: list all columns in db |
Date: | 2007-06-08 07:34:46 |
Message-ID: | faa313130706080034l796db1f3h5d54ed7ca35be434@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
> >
>
>
From: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
---|---|
To: | Jon Sime <jsime(at)mediamatters(dot)org> |
Cc: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: list all columns in db |
Date: | 2007-06-11 20:56:17 |
Message-ID: | E775818D-52BC-4C06-AF5F-E340A1C61AC2@2xlp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -