Nothing fancy today, just a quickie as indicated in the subject line of the post…
set serverout on format wrapped
declare
l_level number := 0;
--get only tables with no parent tables
cursor main is
select distinct table_name,constraint_name
from user_constraints
where table_name not in (select table_name
from user_constraints
where r_constraint_name is not null)
and constraint_type = 'P';
procedure get_constraints (i_level in out number, p_pk in varchar2, p_table in varchar2) is
l_level number := 0;
begin
l_level := i_level;
for c_rec in (select constraint_name, table_name
from user_constraints
where table_name in (select table_name
from user_constraints
where r_constraint_name = p_pk
and table_name <> p_table)
and constraint_type = 'P') loop
i_level := l_level;
dbms_output.put_line(rpad(' ',to_char(i_level),' ')||' '||p_table||' '||c_rec.table_name);
i_level := i_level + 2;
get_constraints(i_level, c_rec.constraint_name, c_rec.table_name);
end loop;
end;
begin
for c_rec in main loop
get_constraints(l_level,c_rec.constraint_name, c_rec.table_name);
l_level := 0;
end loop;
end;
/