select TABLE_NAME from user_constraints where CONSTRAINT_NAME in(select R_CONSTRAINT_NAME from user_constraints where TABLE_NAME = '你的父表' AND CONSTRAINT_TYPE = 'R');
这是我以前整理的一个SQL,有参考价值:with db_constraints as (SELECT/*+materialize*/ DC.CONSTRAINT_NAME, DC.TABLE_NAME, DC.OWNER, DC.R_CONSTRAINT_NAME, (SELECT DC1.TABLE_NAME FROM DBA_CONSTRAINTS DC1 WHERE DC1.OWNER = DC.R_OWNER AND DC.R_CONSTRAINT_NAME = DC1.CONSTRAINT_NAME AND DC1.CONSTRAINT_TYPE = 'P') PARENT_TABLE FROM DBA_CONSTRAINTS DC WHERE DC.OWNER = upper('&owner') AND DC.CONSTRAINT_TYPE = 'R' ), TB AS ( SELECT /*+materialize(T)*/ LEVEL LVL, PARENT_TABLE, TABLE_NAME FROM db_constraints T CONNECT BY PARENT_TABLE = PRIOR TABLE_NAME AND R_CONSTRAINT_NAME != PRIOR R_CONSTRAINT_NAME START WITH PARENT_TABLE =upper('&table_name') ) SELECT PARENT_TABLE, LVL, T.TABLE_NAME CHILD_TABLE FROM TB T;
CONSTRAINT_NAME列:约束名称
R_CONSTRAINT_NAME列:外键约束名称
CONSTRAINT_TYPE列:约束类型 其中值为'P'代表主键约束 值为'R'代表外键约束
(SELECT/*+materialize*/ DC.CONSTRAINT_NAME,
DC.TABLE_NAME,
DC.OWNER,
DC.R_CONSTRAINT_NAME,
(SELECT DC1.TABLE_NAME
FROM DBA_CONSTRAINTS DC1
WHERE DC1.OWNER = DC.R_OWNER
AND DC.R_CONSTRAINT_NAME = DC1.CONSTRAINT_NAME
AND DC1.CONSTRAINT_TYPE = 'P') PARENT_TABLE
FROM DBA_CONSTRAINTS DC
WHERE DC.OWNER = upper('&owner')
AND DC.CONSTRAINT_TYPE = 'R'
),
TB AS
(
SELECT /*+materialize(T)*/
LEVEL LVL,
PARENT_TABLE,
TABLE_NAME
FROM db_constraints T
CONNECT BY PARENT_TABLE = PRIOR TABLE_NAME
AND R_CONSTRAINT_NAME != PRIOR R_CONSTRAINT_NAME
START WITH PARENT_TABLE =upper('&table_name')
)
SELECT
PARENT_TABLE,
LVL,
T.TABLE_NAME CHILD_TABLE
FROM TB T;