Query to View all the Parent and Child Tables - Oracle

To View all the Child tables for a Given table

 SELECT rownum,LPAD(' ',4*(LEVEL-1)) || table1 || ' <-- ' || table2 tables, table2_fkey

FROM

  (SELECT a.table_name table1, b.table_name table2, b.constraint_name table2_fkey

  FROM user_constraints a, user_constraints b 

  WHERE a.constraint_type IN('P', 'U') AND A.OWNER=B.OWNER AND A.OWNER='PCISDBA' 

  AND b.constraint_type = 'R' 

  AND a.constraint_name = b.r_constraint_name 

  AND a.table_name != b.table_name

  AND b.table_name <> '&TABLE_NAME')

CONNECT BY PRIOR  table2 = table1  AND LEVEL <= 5

START WITH table1 = '&TABLE_NAME' ;

To View all the Parent and Child Tables for a Given table

select p.table_name Parent , c.table_name Child ,p.constraint_name

from user_constraints p , user_constraints c

where c.table_name = '&TABLE_NAME'

and c.constraint_type = 'R'

and p.constraint_name = c.r_constraint_name

union

select p.table_name Parent , c.table_name Child ,p.constraint_name

from user_constraints p, user_constraints c

where p.table_name = '&TABLE_NAME'

and c.constraint_type = 'R'

and p.constraint_name = c.r_constraint_name 

order by 2;

To view all the table Relations in a user.

with pur as (
  select table_name, constraint_type, constraint_name, r_constraint_name,
    max(decode(constraint_type,'R',1,0)) over(partition by table_name) is_r
  from user_constraints 
  where constraint_type in ('P', 'U', 'R')
)
, son_dad as (
  select distinct s.table_name son, d.table_name dad, d.constraint_type
  from (select * from pur where constraint_type = 'R' or is_r = 0) s
  left join pur d
    on s.r_constraint_name = d.constraint_name
    and s.table_name != d.table_name
)
select level lvl, son, dad, constraint_type
from son_dad
start with dad is null
connect by dad = prior son
order siblings by dad, son;