// finds child table in recursive . input parent table
Prompt By default starting tables are those that does not referenced other one
Accept Table_ CHAR PROMPT "Starting tables (% allowed) ? "
Set Termout OFF
Column table_ NOPRINT NEW_VALUE Table_
Select nvl(upper('&Table_'),'*NO REF*') table_ from dual
/
Set Termout ON
Column line_ FORMAT A120 HEADING "Foreign Key Tree"
Prompt
Set feedback off
With
data as (
/* Tables/FK along with the referenced table/constraints */
select decode(b.table_name, a.table_name, '*SELF REF*', b.table_name)
table_name,
b.constraint_name,
a.table_name r_table_name,
a.constraint_name r_unq_name
from user_constraints a, user_constraints b
where b.r_owner = a.owner
and b.r_constraint_name = a.constraint_name
and b.constraint_type = 'R'
and a.constraint_type in ('P', 'U')
union all
/* Referenced tables that referenced noone */
select a.table_name, null, null, null
from user_constraints a
where exists ( select 1 from user_constraints b
where b.r_owner = a.owner
and b.r_constraint_name = a.constraint_name
and b.constraint_type = 'R'
and a.constraint_type in ('P', 'U') )
and not exists ( select 1 from user_constraints b
where b.table_name = a.table_name
and b.constraint_type = 'R' )
)
Select lpad(' ',3*(level-1)) ||
decode(table_name, '*SELF REF*', r_table_name, table_name) ||
decode(r_table_name,
null, '',
' ('||constraint_name||' ref. '||
r_table_name||'/'||r_unq_name||')' )||
decode(table_name,
'*SELF REF*', ' ...',
decode(connect_by_iscycle, 1, ' ...'))
from data
connect by nocycle prior table_name = r_table_name
start with ( '&Table_' = '*NO REF*' and r_table_name is null )
or ( '&Table_' != '*NO REF*' and table_name like '&Table_' )
/
