Thread: find all tables with a specific column name?
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
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
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
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 -- Showme 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.columnswhere 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)
>>> 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