Finding relationship between tables 1

[peterneve]

Hi,

I need to look at a large database. Is there any simple way to find all the relationships between the tables of a given schema? Is there a simple SQL command or something like that?

Thanks.

 

[gunjan14]

Hi,

You can execute this command or modify to some extent to acheive what you want

select a.column_name, 
       a.table_name, 
       'REFERS', 
       c.table_name, 
       c.column_name
from   user_cons_columns a, 
       user_constraints b, 
       user_cons_columns c
where  a.constraint_name = b.constraint_name
and    a.table_name = <TABLE_NAME>
and    b.table_name = <TABLE NAME>
and    b.r_constraint_name = c.constraint_name
and    b.constraint_type = 'R';

This query will give you all the foreign key on 'TABLE_NAME' and the corresponding columns. But beware that it gives a cartesian product for a foreign key.

Example

create table temp_gunj_1
(
 P1_COL1 NUMBER,
 G1_COL2 NUMBER,
 G1_COL3 NUMBER
);
create table temp_gunj_2
(
 P2_COL1 NUMBER
 G2_COL2 NUMBER NOT NULL,
 G2_COL3 NUMBER NOT NULL,
 G2_COL4 NUMBER
);
alter table temp_gunj_2 add constraint pk_temp_gunj_2 
    primary key (g2_col2, g2_col3) enable;

alter table temp_gunj_1 add constraint fk_temp_gunj_1 
    FOREIGN KEY (g1_col2,g1_col3) 
    references temp_gunj_2 (g2_col2,g2_col3);

Upon executing of the query above:

COLUMN_NAME TABLE_NAME  'REFER TABLE_NAME  COLUMN_NAME
----------- ----------- ------ ----------  ------------
G1_COL2     TEMP_GUNJ_1 REFERS TEMP_GUNJ_2 G2_COL2
G1_COL3     TEMP_GUNJ_1 REFERS TEMP_GUNJ_2 G2_COL2
G1_COL2     TEMP_GUNJ_1 REFERS TEMP_GUNJ_2 G2_COL3
G1_COL3     TEMP_GUNJ_1 REFERS TEMP_GUNJ_2 G2_COL3

If you want which foreign kex refers to the current table then you can issue this query

select c.table_name, 
       c.column_name, 
       'IS REFERRED BY',
       a.column_name, 
       a.table_name 
from   user_cons_columns a, 
       user_constraints b, 
       user_cons_columns c
where  a.constraint_name = b.constraint_name
and    c.table_name = 'TABLE_NAME'
and    b.r_constraint_name = c.constraint_name
and    b.constraint_type = 'R';

It again gives the cartesian product.

TABLE_NAME  COLUMN_ 'ISREFERREDBY' COLUMN_NAME TABLE_NAME
            NAME  
----------- ------- -------------- ----------- ----------
TEMP_GUNJ_2 G2_COL2 IS REFERRED BY G1_COL2     TEMP_GUNJ_1
TEMP_GUNJ_2 G2_COL2 IS REFERRED BY G1_COL3     TEMP_GUNJ_1
TEMP_GUNJ_2 G2_COL3 IS REFERRED BY G1_COL2     TEMP_GUNJ_1
TEMP_GUNJ_2 G2_COL3 IS REFERRED BY G1_COL3     TEMP_GUNJ_1

Hope this helps. I know it is crude but hope that it helps to some extent


Regards,
Gunjan

 

[alexhu]

Easiest way is to use something like Visio to give you a diagram

Alex

 

[peterneve]

Thanks guys