Oracle 判斷表被多少外鍵引用：雙步查詢法 (USER_CONSTRAINTS + USER_CONS_COLUMNS)

需求：快速統計某表被多少張子表通過外鍵引用。標準數據字典兩步走，秒級出結果。

步驟 1：查詢目標表的主鍵約束名

SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'YOUR_TABLE_NAME'
  AND constraint_type = 'P';  -- P = Primary Key

記錄返回的 CONSTRAINT_NAME (如 PK_EMP)。

步驟 2：查詢所有引用該主鍵的外鍵

SELECT b.table_name AS child_table,
       b.column_name AS child_column,
       a.constraint_name AS fk_name,
       a.status AS fk_status
FROM user_constraints a
JOIN user_cons_columns b ON a.constraint_name = b.constraint_name
WHERE a.r_constraint_name = 'PK_EMP'  -- 步驟 1 得到的主鍵名
  AND a.constraint_type = 'R';        -- R = Referential (Foreign Key)

合併版單條 SQL (直接輸出被引用詳情)

SELECT uc_child.table_name AS child_table,
       ucc.column_name AS child_column,
       uc_child.constraint_name AS fk_name,
       uc_child.status AS fk_status,
       uc_parent.table_name AS parent_table
FROM user_constraints uc_parent
JOIN user_constraints uc_child ON uc_child.r_constraint_name = uc_parent.constraint_name
JOIN user_cons_columns ucc ON ucc.constraint_name = uc_child.constraint_name
WHERE uc_parent.table_name = 'YOUR_TABLE_NAME'
  AND uc_parent.constraint_type = 'P'
  AND uc_child.constraint_type = 'R';

擴展：DBA 視圖版 (跨 Schema 查詢)

SELECT owner, table_name, constraint_name
FROM dba_constraints
WHERE r_constraint_name IN (
  SELECT constraint_name FROM dba_constraints
  WHERE table_name = 'YOUR_TABLE' AND owner = 'SCHEMA_OWNER' AND constraint_type = 'P'
)
AND constraint_type = 'R';

關鍵字典視圖說明