查詢外鍵欄位信息

查詢外鍵欄位信息
SET pagesize 1000
COL 外鍵擁有者 FOR A20
COL 外鍵表 FOR A30
COL 外鍵列 FOR A20
COL 主鍵擁有者 FOR A20
COL 主鍵表 FOR A30
COL 主鍵列 FOR A20
COL 外鍵名 FOR A30
COL 主鍵名 FOR A30

SELECT A.OWNER 外鍵擁有者,
A.TABLE_NAME 外鍵表,
C.COLUMN_NAME 外鍵列,
B.OWNER 主鍵擁有者,
B.TABLE_NAME 主鍵表,
D.COLUMN_NAME 主鍵列,
C.CONSTRAINT_NAME 外鍵名,
D.CONSTRAINT_NAME 主鍵名
FROM DBA_CONSTRAINTS A,
DBA_CONSTRAINTS B,
DBA_CONS_COLUMNS C, --外鍵表
DBA_CONS_COLUMNS D --主鍵表　　
WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.CONSTRAINT_TYPE = 'R'
AND B.CONSTRAINT_TYPE = 'P'
AND A.R_OWNER = B.OWNER
AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND B.CONSTRAINT_NAME = D.CONSTRAINT_NAME
AND A.OWNER = C.OWNER
AND A.TABLE_NAME = C.TABLE_NAME
AND B.OWNER = D.OWNER
AND B.TABLE_NAME = D.TABLE_NAME
AND B.OWNER IN ('OWNERNAME');