Thread: 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
On 07/06/07, Jon Sime <jsime@mediamatters.org> wrote:
Is there any easy way to remove the views from the query?
Peter.
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
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
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@gmail.com> wrote:
On 07/06/07, Jon Sime <jsime@mediamatters.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
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 | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -