Thread: Get all table names that have a specific column

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

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    ===

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
> 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
>

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