Home » SQL & PL/SQL » SQL & PL/SQL » Finding Tables with similar columns
Finding Tables with similar columns [message #192225] Mon, 11 September 2006 08:33 Go to next message
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Oracle on Linux -
i have primary keys on certain tables, many of which are composite primary keys.
in order to put foreign keys which are missing at many places i want to find out tables which have columns similar to that having primary keys for other tables

create table m1(a number, b number, c number, d number)

alter table m1 add constraint pk_m1 primary key(a,b,c)

create table m2(a number, b number)

create table m3(a number, b number, c number,e number, f varchar2)

create table m4(a number, b number, c number,g number, h number)

now m1 table has primary key on (a,b,c)
Table m3 has all these primary key columns in it.

so the required script shall return following output

Master child
m1 m3
m1 m4

However the scrpit shall not return table m2 since it misses column c in primary key

Also this shall happen for all tables having primary key in it.

Thanks and Regards,
Re: Finding Tables with similar columns [message #192251 is a reply to message #192225] Mon, 11 September 2006 10:02 Go to previous message
Messages: 121
Registered: August 2006
Senior Member
look in to views dba_cons_columns and dba_constraints and construct query to pull necessary data.

Previous Topic: Logical Delete
Next Topic: splliting the string
Goto Forum:

Current Time: Wed Aug 23 03:01:55 CDT 2017

Total time taken to generate the page: 0.10592 seconds