查询外键字段信息

查询外键字段信息
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');

转载于:https://www.cnblogs.com/ritchy/p/11053060.html