我喜欢使用直接的SQL查询来实现这一点，而不是在SQL Developer应用程序中瞎折腾。

我是这么做的。最好通读这篇文章，了解发生了什么，这样你就可以调整它来满足你的需求……

WITH all_primary_keys AS (
  SELECT constraint_name AS pk_name,
         table_name
    FROM all_constraints
   WHERE owner = USER
     AND constraint_type = 'P'
)
  SELECT ac.table_name || ' table has a foreign key called ' || upper(ac.constraint_name)
         || ' which references the primary key ' || upper(ac.r_constraint_name) || ' on table ' || apk.table_name AS foreign_keys
    FROM all_constraints ac
         LEFT JOIN all_primary_keys apk
                ON ac.r_constraint_name = apk.pk_name
   WHERE ac.owner = USER
     AND ac.constraint_type = 'R'
     AND ac.table_name = nvl(upper(:table_name), ac.table_name)
ORDER BY ac.table_name, ac.constraint_name
;

SELECT DISTINCT table_name, 
                constraint_name, 
                column_name, 
                r_table_name, 
                position, 
                constraint_type 
FROM   (SELECT uc.table_name, 
               uc.constraint_name, 
               cols.column_name, 
               (SELECT table_name 
                FROM   user_constraints 
                WHERE  constraint_name = uc.r_constraint_name) r_table_name, 
               (SELECT column_name 
                FROM   user_cons_columns 
                WHERE  constraint_name = uc.r_constraint_name 
                       AND position = cols.position)           r_column_name, 
               cols.position, 
               uc.constraint_type 
        FROM   user_constraints uc 
               inner join user_cons_columns cols 
                       ON uc.constraint_name = cols.constraint_name 
        WHERE  constraint_type != 'C') 
START WITH table_name = '&&tableName' 
           AND column_name = '&&columnName' 
CONNECT BY NOCYCLE PRIOR table_name = r_table_name 
                         AND PRIOR column_name = r_column_name; 

我喜欢使用直接的SQL查询来实现这一点，而不是在SQL Developer应用程序中瞎折腾。

我是这么做的。最好通读这篇文章，了解发生了什么，这样你就可以调整它来满足你的需求……

WITH all_primary_keys AS (
  SELECT constraint_name AS pk_name,
         table_name
    FROM all_constraints
   WHERE owner = USER
     AND constraint_type = 'P'
)
  SELECT ac.table_name || ' table has a foreign key called ' || upper(ac.constraint_name)
         || ' which references the primary key ' || upper(ac.r_constraint_name) || ' on table ' || apk.table_name AS foreign_keys
    FROM all_constraints ac
         LEFT JOIN all_primary_keys apk
                ON ac.r_constraint_name = apk.pk_name
   WHERE ac.owner = USER
     AND ac.constraint_type = 'R'
     AND ac.table_name = nvl(upper(:table_name), ac.table_name)
ORDER BY ac.table_name, ac.constraint_name
;

这已经在产品中存在多年了——尽管在2011年的产品中还没有。

但是，只需单击Model页面。

请确保您使用的版本至少为4.0(2013年发布)才能访问此功能。

在下面的查询中用emp替换[Your TABLE]

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
  from all_constraints 
 where constraint_type='R'
   and r_constraint_name in (select constraint_name 
                               from all_constraints 
                              where constraint_type in ('P','U') 
                                and table_name='[YOUR TABLE]');

替换下面的MY_OWNER_NAME和MY_TABLE_NAME，你就可以递归了:

DECLARE
FUNCTION list_all_child_tables_and_constraints(asked_table_name in VARCHAR2, parent_table_name in VARCHAR2)
RETURN VARCHAR2 IS
    current_path VARCHAR2(100);
BEGIN
    FOR item IN
    (SELECT fk.TABLE_NAME, constraint_parent.FK FK1, constraint_child.FK FK2
        FROM all_constraints fk, all_constraints pk,
            (SELECT acc.CONSTRAINT_NAME, LISTAGG(acc.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY acc.COLUMN_NAME) AS FK
                FROM ALL_CONS_COLUMNS acc
                WHERE acc.OWNER = 'MY_OWNER_NAME'
                GROUP BY acc.CONSTRAINT_NAME) constraint_parent,
            (SELECT acc.CONSTRAINT_NAME, LISTAGG(acc.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY acc.COLUMN_NAME) AS FK
                FROM ALL_CONS_COLUMNS acc
                WHERE acc.OWNER = 'MY_OWNER_NAME'
                GROUP BY acc.CONSTRAINT_NAME) constraint_child
        WHERE pk.owner = fk.r_owner
            AND pk.constraint_name = fk.r_constraint_name
            AND fk.constraint_type = 'R'
            AND pk.table_name = asked_table_name
            AND constraint_parent.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
            AND constraint_child.CONSTRAINT_NAME = fk.R_CONSTRAINT_NAME
            AND pk.owner = 'MY_OWNER_NAME'
            AND fk.owner = 'MY_OWNER_NAME')
    LOOP
        current_path := parent_table_name || ' // ' || item.TABLE_NAME;
        DBMS_OUTPUT.PUT_LINE(current_path);
        DBMS_OUTPUT.PUT_LINE('     [' || item.FK1 || ']  [' || item.FK2 || ']');
        DBMS_OUTPUT.PUT_LINE('');
        current_path := list_all_child_tables_and_constraints(item.TABLE_NAME, current_path);

    END LOOP;
    RETURN '-----------FINISHED-----------';

EXCEPTION
    WHEN OTHERS THEN
        RETURN '-----------FINISHED-----------';
END list_all_child_tables_and_constraints;
BEGIN
DBMS_OUTPUT.PUT_LINE(list_all_child_tables_and_constraints('MY_TABLE_NAME', ''));
END;