Re: Get all table names that have a specific column

Re: Get all table names that have a specific column

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

Re: Get all table names that have a specific column

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

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

Re: Get all table names that have a specific column

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

Re: Get all table names that have a specific column

Re: 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"  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
>