Generate a DOT graph of Foreign Keys dependencies
27/12/20 13:31
Recursive query generating a DOT graph of the foreign keys dependencies of the USER schema.
select t from (
-- header of the DOT graph
select 0 as rn, 'digraph G {' || chr(10) || ' rankdir=LR;' || chr(10) || ' node[fontsize=14];' || chr(10) as t from dual
union all
-- all tables that are not the basis of materialized views are susceptible to have FK
-- so even the tables without FK will be listed
select 1 as rn, table_name || '[shape=plaintext];' from user_tables dt where
not exists(select 1 from user_mviews dm where dm.mview_name = dt.table_name)
union all
-- the resursive query walking the tree of FK dependencies starting with the ones having no dependencies pointing on them
-- distinct needed in case of cycles (as HR schema has...)
select distinct 2, b1.table_name || ' -> ' || c1.table_name || '[ label ="' || b1.constraint_name || '", fontsize=8 ];'
from user_constraints b1
join user_cons_columns c1 on b1.r_constraint_Name = c1.constraint_name and b1.r_owner = c1.owner
where
b1.constraint_type = 'R'
start with
c1.table_name in (
select table_name from user_tables at
where not exists(
select 1 from user_constraints ac
where
ac.table_name = at.table_name
and ac.constraint_type = 'R'
)
)
connect by nocycle c1.table_name = prior b1.table_name
union all
-- footer of the DOT graph
select 3 as rn, chr(10) || '}' || chr(10) from dual
)
order by rn, t;