Is is posible to search the database for column names 1

[thewhistler1]

I work with a data base that has about 400 tables and sometimes it is hard figuring out how tables relate to other tables. What I would like to do if possible is write a script that searches the column names.

example

Table name - team_member
Columns - team_member_oid, name,address,phone, etc...

How can I search the datebase for the a column name for example team_member_oid to find all the tables that contain team_member_oid?

Any advice on this would be much appreciated. Thanks

 

[feherke]

Hi

To write a script ? I see no reason. In [tt]psql[/tt] the [tt]\dS[/tt] command displays the system tables. From there you can pick what you need and use a single [tt]select[/tt] :

[b]select[/b]
c.relname [b]as[/b] [i]"table name"[/i]

[b]from[/b] pg_attribute a
[b]inner[/b] [b]join[/b] pg_class c [b]on[/b] c.oid=a.attrelid

[b]where[/b] a.attstattarget!=0
[b]and[/b] a.attname=[i]'[/i][green][i]team_member_oid[/i][/green][i]'[/i]

Tested with PostgreSQL 7.3.

Feherke.

http://rootshell.be/~feherke/

 

[thewhistler1]

Thanks