Creating a dependency graph for a table with a recursive query

Question

I am trying to build a dependency graph of tables based on foreign keys between them. This graph should start with the name of an arbitrary table as its root. I could, given the name of the table, look for tables that reference it using the all_constraints view, then look for tables that reference them, and so on, but that would be terribly inefficient. I wrote a recursive query that does this for all tables, but when I add:

START WITH Table_Name=:tablename 

It does not return the whole tree.

Answer 1

  select parent, child, level from ( select parent_table.table_name parent, child_table.table_name child from user_tables parent_table, user_constraints parent_constraint, user_constraints child_constraint, user_tables child_table where parent_table.table_name = parent_constraint.table_name and parent_constraint.constraint_type IN( 'P', 'U' ) and child_constraint.r_constraint_name = parent_constraint.constraint_name and child_constraint.constraint_type = 'R' and child_table.table_name = child_constraint.table_name and child_table.table_name != parent_table.table_name ) start with parent = 'DEPT' connect by prior child = parent 

should work (replace the table name, of course), assuming everything is in the same schema. Use the DBA_ versions of the data dictionary tables and the conditions for the OWNER and R_OWNER columns if you need to handle dependencies between schemas. Upon further consideration, this does not take into account self-referential constraints (i.e. the constraint on the EMP table, which the MGR column refers to the EMPNO column), so you will have to change the code to handle this case if you need to deal with self-referential constraints.

For testing purposes, I added several new tables to the SCOTT schema, which also reference the DEPT table (including grandson dependency)

 SQL> create table dept_child2 ( 2 deptno number references dept( deptno ) 3 ); Table created. SQL> create table dept_child3 ( 2 dept_child3_no number primary key, 3 deptno number references dept( deptno ) 4 ); Table created. SQL> create table dept_grandchild ( 2 dept_child3_no number references dept_child3( dept_child3_no ) 3 ); Table created. 

and confirmed that the request returned the expected output

 SQL> ed Wrote file afiedt.buf 1 select parent, child, level from ( 2 select parent_table.table_name parent, child_table.table_name child 3 from user_tables parent_table, 4 user_constraints parent_constraint, 5 user_constraints child_constraint, 6 user_tables child_table 7 where parent_table.table_name = parent_constraint.table_name 8 and parent_constraint.constraint_type IN( 'P', 'U' ) 9 and child_constraint.r_constraint_name = parent_constraint.constraint_name 10 and child_constraint.constraint_type = 'R' 11 and child_table.table_name = child_constraint.table_name 12 and child_table.table_name != parent_table.table_name 13 ) 14 start with parent = 'DEPT' 15* connect by prior child = parent SQL> / PARENT CHILD LEVEL ------------------------------ ------------------------------ ---------- DEPT DEPT_CHILD3 1 DEPT_CHILD3 DEPT_GRANDCHILD 2 DEPT DEPT_CHILD2 1 DEPT EMP 1 

Answer 2

The easiest way to do this is to copy all the FK information into a simple table with two columns (parent, child), and then use the following algorithm:

 while (rows left in that table) list = rows where table name exists in child but not in parent print list remove list from rows 

what all. Basically, you first print and delete all nodes that are not dependent on anything. After that, some other nodes will be freed, and you can repeat the process.

PS Make sure that you do not insert self-regulation tables in the source list (child = parent)