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
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,
Oracle on Linux - 9.2.0.7.0
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

Ex.
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,
Pratap
Re: Finding Tables with similar columns [message #192251 is a reply to message #192225] Mon, 11 September 2006 10:02 Go to previous message
email.sandeepnaik
Messages: 121
Registered: August 2006
Senior Member
look in to views dba_cons_columns and dba_constraints and construct query to pull necessary data.

Sandeep
Previous Topic: To get alternative row from table
Next Topic: Running dbms_stats is taking very long time
Goto Forum:
  


Current Time: Sun Dec 04 00:27:06 CST 2016

Total time taken to generate the page: 0.06409 seconds