Обсуждение: 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