Thread: Find all instances of a column in the entire database.

Hi Everyone,

I am hoping that you might be able to give me some assistance with the  
following task!
I have a database with nearly 200 tables and I need to find all tables  
that contain a column of myColumnName.

I was hoping there might be a built-in function for this task, but I  
have been unable to find any information through our good friend Mr.  
Google or by perusing the fine manual.

Thanks in advance for any thoughts you might have.


- Beau

On Fri, May 16, 2008 at 11:51:20PM +1000, Gavin 'Beau' Baumanis wrote:
> I am hoping that you might be able to give me some assistance with the  
> following task!
> I have a database with nearly 200 tables and I need to find all tables  
> that contain a column of myColumnName.

select * from information_schema.columns where column_name = 'myColumnName';

depesz

Hi depesz,

Thanks very much!
That works wonderfully well...

Is this listed in the manual anywhere? because after two hours of  
reading, I didn't find it anywhere!

None the less - thanks again.


- Beau


On 16/05/2008, at 11:56 PM, hubert depesz lubaczewski wrote:

> On Fri, May 16, 2008 at 11:51:20PM +1000, Gavin 'Beau' Baumanis wrote:
>> I am hoping that you might be able to give me some assistance with  
>> the
>> following task!
>> I have a database with nearly 200 tables and I need to find all  
>> tables
>> that contain a column of myColumnName.
>
> select * from information_schema.columns where column_name =  
> 'myColumnName';
>
> depesz

am  Sat, dem 17.05.2008, um  0:04:05 +1000 mailte Gavin 'Beau' Baumanis folgendes:
> Hi depesz,
> 
> Thanks very much!
> That works wonderfully well...
> 
> Is this listed in the manual anywhere? because after two hours of  
> reading, I didn't find it anywhere!


Of course, the whole information schema:
http://www.postgresql.org/docs/current/static/information-schema.html



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

gavinb@eclinic.com.au ("Gavin 'Beau' Baumanis") writes:
> I am hoping that you might be able to give me some assistance with the
> following task!
> I have a database with nearly 200 tables and I need to find all tables
> that contain a column of myColumnName.
>
> I was hoping there might be a built-in function for this task, but I
> have been unable to find any information through our good friend Mr.
> Google or by perusing the fine manual.
>
> Thanks in advance for any thoughts you might have.

I have a schema which has a number of attributes called "name_id":

metadata=# select n.nspname, r.relname from pg_class r, pg_namespace n, pg_attribute a where a.attname = 'name_id' and
r.oid= attrelid and n.oid = relnamespace;nspname  |       relname       
 
----------+---------------------registry | redactedregistry | redactedregistry | redactedregistry | redactedregistry |
redactedregistry| redactedregistry | redactedregistry | redactedregistry | redactedregistry | redactedregistry |
redactedregistry| redactedregistry | redactedregistry | redactedregistry | redactedregistry | redactedregistry |
redactedregistry| redactedregistry | redactedregistry | redactedregistry | redactedregistry | redactedregistry |
redacted
(23 rows)

Change 'name_id' to 'myColumnName' and you should get what you're
expecting...
-- 
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/lsf.html
Signs of a  Klingon Programmer -  17. "Klingon multitasking systems do
not  support "time-sharing". When a Klingon  program  wants to run, it
challenges the scheduler in hand-to-hand combat and owns the machine."