Recursive query for table dependencies is not recursing not as much as I'd like

I had an idea that I could write a query to find all the descendent tables of a root table, based on foreign keys.

Query looks like this:

select level, lpad(' ', 2 * (level - 1)) || uc.table_name as "TABLE", uc.constraint_name, uc.r_constraint_name
from all_constraints uc
where uc.constraint_type in ('R', 'P')
start with uc.table_name = 'ROOT_TAB'
connect by nocycle prior uc.constraint_name = uc.r_constraint_name
order by level asc;

The results I get look like this:

        1   ROOT_TAB        XPKROOTTAB  
        1   ROOT_TAB        R_20           XPKPART_TAB
        2     CHILD_TAB_1   R_40           XPKROOTTAB
        2     CHILD_TAB_2   R_115          XPKROOTTAB
        2     CHILD_TAB_3   R_50           XPKROOTTAB

This result is all the child tables of ROOT_TAB, but the query do开发者_如何学运维es not recurse to the children of CHILD_TAB_1, CHILD_TAB_2, or CHILD_TAB_3.

Recursive queries are new to me so I'm guessing I'm missing something in the connect by clause, but I'm drawing a blank here. Is it actually possible to get the full hierarchy of ROOT_TAB in a single query, or am I better off wrapping the query in a recursive procedure?

---

You want something like this:

select t.table_name, level,lpad(' ', 2 * (level - 1))||t.table_name 
from user_tables t
join user_constraints c1 
    on (t.table_name = c1.table_name 
    and c1.constraint_type in ('U', 'P'))
left join user_constraints c2 
    on (t.table_name = c2.table_name 
    and c2.constraint_type='R')
start with t.table_name = 'ROOT_TAB'
connect by prior c1.constraint_name = c2.r_constraint_name

The problem with the original query is that uc.constraint_name for the child table is the name of the foreign key. That is fine for connecting the first child to the root table, but it is not what you need to connect the children on the second level to the first. That is why you need to join against the constraints twice -- once to get the table's primary key, once to get the foreign keys.

As an aside, if you are going to be querying the all_* views rather than the user_* views, you generally want to join them on table_name AND owner, not just table_name. If multiple schemas have tables with the same name, joining on just table_name will give incorrect results.

---

For the case with multiple schemas and multiple root tables, try something like:

WITH constraining_tables AS (SELECT owner, constraint_name, table_name
                               FROM all_constraints
                              WHERE owner LIKE 'ZZZ%' AND constraint_type IN ('U', 'P')),
     constrained_tables AS (SELECT owner, constraint_name, table_name, r_owner, r_constraint_name
                              FROM all_constraints
                             WHERE owner LIKE 'ZZZ%' AND constraint_type = 'R'),
     root_tables AS (SELECT owner, table_name FROM constraining_tables
                     MINUS
                     SELECT owner, table_name FROM constrained_tables)
    SELECT c1.owner || '.' || c1.table_name, LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || c1.owner || '.' || c1.table_name
      FROM    constraining_tables c1
           LEFT JOIN
              constrained_tables c2
           ON c1.owner = c2.owner AND c1.table_name = c2.table_name
START WITH c1.owner || '.' || c1.table_name IN (SELECT owner || '.' || table_name FROM root_tables)
CONNECT BY PRIOR c1.constraint_name = c2.r_constraint_name

---

After deep deep investigation, I made my own version that processes all tables and retreives the table's max level in hierarchy (it reads all schemas, taking also into account the tables with no parent-child relationship, that will be at level 1 along with root ones). If you have access, use dba_ tables instead of all_ ones.

      WITH hier AS (
                         SELECT child_table owner_table_name
                              , LEVEL lvl
                              , LPAD (' ', 4 * (LEVEL - 1)) || child_table indented_child_table
                              , sys_connect_by_path( child_table, '|' )  tree
                           FROM (
/*----------------------------------------------------------------------*/
/* Retrieve all tables. Set them as the Child column, and set their     */
/* Parent Column to NULL. This is the root list (first iteration)       */
/*----------------------------------------------------------------------*/
                                  SELECT NULL                              parent_table
                                       , a.owner || '.' || a.table_name    child_table
                                    FROM all_tables a
                                   UNION
/*----------------------------------------------------------------------*/
/* List of all possible Parent-Child relations. This table is used as   */
/* a link list, to link the current iteration with the next one, from   */
/* root to last child (last child is what we are interested to find).   */
/*----------------------------------------------------------------------*/
                                  SELECT p.owner   || '.' || p.table_name            parent_table
                                       , c.owner   || '.' || c.table_name            child_table
                                    FROM all_constraints p, all_constraints c
                                   WHERE p.owner || '.' || p.constraint_name = c.r_owner || '.' || c.r_constraint_name
                                     AND (p.constraint_type = 'P' OR p.constraint_type = 'U')
                                     AND c.constraint_type = 'R'
                                )
                     START WITH parent_table IS NULL
/*----------------------------------------------------------------------*/
/* NOCYCLE prevents infinite loops (i.e. self referencing table constr) */
/*----------------------------------------------------------------------*/
                     CONNECT BY NOCYCLE PRIOR child_table = parent_table
                   )
                     SELECT *
                       FROM hier
                      WHERE (owner_table_name, lvl) IN (   SELECT owner_table_name
                                                                , MAX(lvl)
                                                             FROM hier
                                                         GROUP BY owner_table_name
                                                       );

Edit: There is "kind of" an issue with this query when finding infinite loops.

If we have this tree:

b --> c --> d
b <-- c

it will assign lvl 2 to c as: b --> c and lvl 2 to b as: c --> b

for d, it will detect b --> c --> d so it will assign lvl 3

So as you can see, the problem is inside the loop, the values from outside will always have its max correct lvl