Re: Find same-named columns

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 9 Mar 2002 17:23:04 -0600
Message-ID: <u8z91jgyo.fsf@rcn.com>

On Sat, 9 Mar 2002, rkg100_at_erols.com wrote:
> Select COLUMN_NAME, count(column_name), table_name
> FROM DBA_TABLES
> group by column_name
> having count(column_name) > 1
>
>
> "Halbert09" <junk01_at_optonline.net> wrote in message
> news:338ccebe.0203090848.5e82ee14_at_posting.google.com...

>> What SQL+ query would show me those tables that contain columns whose
>> names match the column-names of columns in other tables of the same
>> schema, and list the tables and matching column-names?  Thanks for
>> your help.
>>
>> Harold

Here's a script I use. I call it "grep_columns".

Combine it with the output of the above query, and you should get what you need, although, heed noone's advice.

accept UserName char PROMPT 'User Name: ' accept GrepString char PROMPT 'Grep String: '

set head off
set head on
column type format A5 wrap
column table_name format A30 wrap
column COLUMN_NAME format A25 wrap
column DATA_TYPE format A10 wrap
select t2.object_type TYPE , t1.table_name, t1.column_name, t1.data_type from all_tab_columns t1,

       all_objects t2

where  t1.table_name = t2.OBJECT_NAME
and    t1.owner = t2.owner
and    t2.owner = upper('&UserName')
AND    t1.COLUMN_NAME like upper('%&GrepString%')

order by t2.object_type, t1.table_name
/

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Sat Mar 09 2002 - 17:23:04 CST