Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <pre><code> 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 </code></pre> <p>should work (replace the table name, of course) assuming that everything is in the same schema. Use the DBA_ versions of the data dictionary tables and conditions for the OWNER and R_OWNER columns if you need to handle cross-schema dependencies. On further reflection, this does not account for self-referential constraints (i.e. a constraint on the EMP table that the MGR column references the EMPNO column) either, so you'd have to modify the code to handle that case if you need to deal with self-referential constraints.</p> <p>For testing purposes, I added a few new tables to the SCOTT schema that also reference the DEPT table (including a grandchild dependency)</p> <pre><code>SQL&gt; create table dept_child2 ( 2 deptno number references dept( deptno ) 3 ); Table created. SQL&gt; create table dept_child3 ( 2 dept_child3_no number primary key, 3 deptno number references dept( deptno ) 4 ); Table created. SQL&gt; create table dept_grandchild ( 2 dept_child3_no number references dept_child3( dept_child3_no ) 3 ); Table created. </code></pre> <p>and verified that the query returned the expected output</p> <pre><code>SQL&gt; 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&gt; / PARENT CHILD LEVEL ------------------------------ ------------------------------ ---------- DEPT DEPT_CHILD3 1 DEPT_CHILD3 DEPT_GRANDCHILD 2 DEPT DEPT_CHILD2 1 DEPT EMP 1 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POBuilding a Table Dependency Graph With A Recursive Query
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJustin Cave
    1. USJustin Cave
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POBuilding a Table Dependency Graph With A Recursive Query
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COWhen I run that query I get `ORA-01437: cannot have join with CONNECT BY`
      singulars
      1. PO
      1. USTulains Córdova
    2. CO@user1598390 - Are you saying that when you run the exact test case I posted that you get an error? Or are you doing something (even slightly) different? Without seeing your code, it is highly unlikely that we're going to be able to do much to help you. You're probably going to need to create a new question where you can post the exact query you're using and, ideally, show it failing with some sample tables that you post.
      singulars
      1. PO
      1. USJustin Cave
    3. COI copy your SQL, paste it into an Oracle client ( PL/SQL Developer ), then hit F8 to run it, and get the error.
      singulars
      1. PO
      1. USTulains Córdova
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload