Get all table names that have a specific column

pgsql stores table names in pg_class.relname, column names in
pg_attribute.attname.
Read the document and you can get the solution.

"Emi Lu" <emilu@cs.concordia.ca> wrote

am 30.09.2005, um 10:55:44 -0400 mailte Emi Lu folgendes:

Greetings,

I am not very familiar with the system views/tables in postgreSQL. I'd like
to get all table names that have a column let's say named "col1".

select table_name from information_schema.columns where column_name = 'col1';

Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

Emi Lu wrote:

Greetings,

I am not very familiar with the system views/tables in postgreSQL. I'd
like to get all table names that have a column let's say named "col1".

For example,
t1 (... col1 varchar(3) ... )
t2 (... col1 varchar(3) ... )
t3 (... ...)

After querying the system tables/views, I can get the result something
like :

tables contain column "col1"
---------------------------------------------
t1
t2
(2 rows)

Thanks a lot,
Emi

Check this posting:
http://archives.postgresql.org/pgsql-admin/2005-03/msg00011.php

Query the pga_columns view for the matches that you are looking for.
eg:
select tablename
from pga_columns
where columnname='col1';

--
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________

On 9/30/05, Emi Lu <emilu@cs.concordia.ca> wrote:

Greetings,

I am not very familiar with the system views/tables in postgreSQL. I'd
like to get all table names that have a column let's say named "col1".

I'd use:

select table_name
from information_schema.columns
where table_schema='my_schema'
and column_name='col1'

Lots of good info here:
http://www.postgresql.org/docs/7.4/static/information-schema.html

Easy way:
SELECT table_name FROM information_schema.columns WHERE column_name = ''
or
SELECT table_name FROM pg_sysviews.pg_user_table_columns WHERE
column_name = ''

If those don't work, select from pg_attribute a join pg_class c on
(c.oid = a.reloid)

On Fri, Sep 30, 2005 at 10:55:44AM -0400, Emi Lu wrote:

Greetings,

I am not very familiar with the system views/tables in postgreSQL. I'd
like to get all table names that have a column let's say named "col1".

For example,
t1 (... col1 varchar(3) ... )
t2 (... col1 varchar(3) ... )
t3 (... ...)

After querying the system tables/views, I can get the result something
like :

tables contain column "col1"
---------------------------------------------
t1
t2
(2 rows)

Thanks a lot,
Emi

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461