From: dmoyer@gpu.com (D. Scott Moyer, Jr.)
Subject: Re: Help! Script needed...
Date: 1996/12/12
Message-ID: <32b00931.5471628@internet.gpuc.com>#1/1
references: <58kmi5$ajf@news2.cais.com> <58lq9j$pr0@newton.pacific.net.sg>
organization: FASTNET(tm) PA/NJ/DE Internet
newsgroups: comp.databases.oracle.tools



On 11 Dec 1996 08:10:27 GMT, N Prabhakar <prabhs@po.pacific.net.sg>
wrote:

>duffy@cais.cais.com (MCC) wrote:
>>Hi Oracle Gurus,
>>
>>   Does anyone have an working script that will list all the tables
>>that have the same column names?  For a particular column name, I need
>>to know what tables have it.  Instead of DESCRIB every tables in the
>>database, a working script will be highly appreciated.
>>
>>
>>Thanks,
>>Edith
>

Try the following and maybe even add an order by.

SELECT ALL SYS.USER_TAB_COLUMNS.TABLE_NAME, 
USER_TAB_COLUMNS_A1.TABLE_NAME, SYS.USER_TAB_COLUMNS.COLUMN_NAME
FROM SYS.USER_TAB_COLUMNS, 
SYS.USER_TAB_COLUMNS USER_TAB_COLUMNS_A1, SYS.USER_OBJECTS
WHERE SYS.USER_OBJECTS.OBJECT_TYPE='TABLE'
 AND
((SYS.USER_TAB_COLUMNS.COLUMN_NAME=USER_TAB_COLUMNS_A1.COLUMN_NAME)
 AND (SYS.USER_TAB_COLUMNS.TABLE_NAME<>USER_TAB_COLUMNS_A1.TABLE_NAME)
 AND (USER_TAB_COLUMNS_A1.TABLE_NAME=SYS.USER_OBJECTS.OBJECT_NAME))

dmoyer@gpu.com