查看表中某个列具有外键的所有表？

Question

这可能吗？如果是，涉及哪些表，可以在哪里查看。

Answer 1

尝试这样的

select uc.table_name 
from sys.user_cons_columns ucc
join sys.user_constraints uc 
on uc.r_constraint_name = ucc.constraint_name
where constraint_type = 'R'
and ucc.table_name = :1
and ucc.column_name = :2

地方

:1 = referenced table
:2 = referenced column

Answer 2

select * from user_constraints where r_constraint_name =<PK_NAME>

其中 是列的主键约束的名称

Answer 3

有两个候选查询可能会有所帮助：

http://www.alberton.info/oracle_meta_info.html

SELECT alc.constraint_name,
          CASE alc.constraint_type
            WHEN 'P' THEN 'PRIMARY KEY'
            WHEN 'R' THEN 'FOREIGN KEY'
            WHEN 'U' THEN 'UNIQUE'
            WHEN 'C' THEN 'CHECK'
          END "constraint_type",
          alc.DELETE_RULE "on_delete",
          CASE alc.deferrable WHEN 'NOT DEFERRABLE' THEN 0 ELSE 1 END "deferrable",
          CASE alc.deferred WHEN 'IMMEDIATE' THEN 1 ELSE 0 END "initially_deferred",
          alc.search_condition,
          alc.table_name,
          cols.column_name,
          cols.position,
          r_alc.table_name "references_table",
          r_cols.column_name "references_field",
          r_cols.position "references_field_position"
     FROM all_cons_columns cols
LEFT JOIN all_constraints alc
       ON alc.constraint_name = cols.constraint_name
      AND alc.owner = cols.owner
LEFT JOIN all_constraints r_alc
       ON alc.r_constraint_name = r_alc.constraint_name
      AND alc.r_owner = r_alc.owner
LEFT JOIN all_cons_columns r_cols
       ON r_alc.constraint_name = r_cols.constraint_name
      AND r_alc.owner = r_cols.owner
      AND cols.position = r_cols.position
    WHERE alc.constraint_name = cols.constraint_name
      AND alc.constraint_name = 'TESTCONSTRAINTS_ID_FK'
      AND alc.table_name = 'TESTCONSTRAINTS2';