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: To get alternative row from table
Next Topic: Running dbms_stats is taking very long time
Goto Forum:

Current Time: Wed Jun 28 12:24:10 CDT 2017

Total time taken to generate the page: 0.12910 seconds