pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfind all tables with a specific column name?
5+ messages / 4 participants
[nested] [flat]
* find all tables with a specific column name?
@ 2006-04-21 16:29 Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 2 replies; 5+ messages in thread
From: Jeff Frost @ 2006-04-21 16:29 UTC (permalink / raw)
To: pgsql-sql
Is there a reasonable way to extract a list of all tables which contain a
specific column name from the system views on 8.1?
For instance, I might want to enumerate all tables with a column named
last_modified.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: find all tables with a specific column name?
@ 2006-04-21 18:03 Bruno Wolff III <bruno@wolff.to>
parent: Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2006-04-21 18:03 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
On Fri, Apr 21, 2006 at 09:29:33 -0700,
Jeff Frost <jeff@frostconsultingllc.com> wrote:
> Is there a reasonable way to extract a list of all tables which contain a
> specific column name from the system views on 8.1?
>
> For instance, I might want to enumerate all tables with a column named
> last_modified.
Take a look at:
http://developer.postgresql.org/docs/postgres/infoschema-columns.html
and
http://developer.postgresql.org/docs/postgres/infoschema-schema.html
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: find all tables with a specific column name?
@ 2006-04-21 18:30 Jeff Frost <jeff@frostconsultingllc.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 5+ messages in thread
From: Jeff Frost @ 2006-04-21 18:30 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
On Fri, 21 Apr 2006, Bruno Wolff III wrote:
> On Fri, Apr 21, 2006 at 09:29:33 -0700,
> Jeff Frost <jeff@frostconsultingllc.com> wrote:
>> Is there a reasonable way to extract a list of all tables which contain a
>> specific column name from the system views on 8.1?
>>
>> For instance, I might want to enumerate all tables with a column named
>> last_modified.
>
> Take a look at:
> http://developer.postgresql.org/docs/postgres/infoschema-columns.html
Thanks Bruno! It appears I can simply do this:
select table_name from information_schema.columns where column_name =
'last_modified';
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: find all tables with a specific column name?
@ 2006-04-21 18:31 George Young <gry@ll.mit.edu>
parent: Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 0 replies; 5+ messages in thread
From: George Young @ 2006-04-21 18:31 UTC (permalink / raw)
To: pgsql-sql
I've found it useful to explore the information_schema schema by doing:
set search_path=information_schema; -- Lets just look at the system tables.
\d -- Show me all the tables.
...
(40 rows)
then
select * from some-likely-looking-table limit 20;
In this case, I quickly found a table called "columns", so you can do:
select table_name from information_schema.columns where
column_name='last_modified';
Of course you could be a wuss and actually read the documentation ;-)
http://www.postgresql.org/docs/8.1/interactive/infoschema-columns.html
-- George Young
On Fri, 21 Apr 2006 09:29:33 -0700 (PDT)
Jeff Frost <jeff@frostconsultingllc.com> wrote:
> Is there a reasonable way to extract a list of all tables which contain a
> specific column name from the system views on 8.1?
>
> For instance, I might want to enumerate all tables with a column named
> last_modified.
>
> --
> Jeff Frost, Owner <jeff@frostconsultingllc.com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 650-780-7908 FAX: 650-649-1954
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: find all tables with a specific column name?
@ 2006-04-22 21:37 Ben K. <bkim@coe.tamu.edu>
parent: Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ben K. @ 2006-04-22 21:37 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-sql
>>> Is there a reasonable way to extract a list of all tables which contain a
>>> specific column name from the system views on 8.1?
>>> For instance, I might want to enumerate all tables with a column named
>>> last_modified.
This is nothing new but if I may, may I add for this thread's completeness
a try from internal tables?
select a.relkind, a.relname from pg_class a inner join pg_attribute b on
a.relfilenode = b.attrelid group by a.relkind, a.relname,
a.relfilenode,b.attname having b.attname='IID';
The result didn't match the one from the information_schema.tables - the
above query included indexes too (relkind=i) while
information_schema.tables included only tables and views (r,v).
Ben K.
Developer
http://benix.tamu.edu
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-04-22 21:37 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-21 16:29 find all tables with a specific column name? Jeff Frost <jeff@frostconsultingllc.com>
2006-04-21 18:03 ` Bruno Wolff III <bruno@wolff.to>
2006-04-21 18:30 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-04-22 21:37 ` Ben K. <bkim@coe.tamu.edu>
2006-04-21 18:31 ` George Young <gry@ll.mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox