mysql外键字段怎么查询_查询外键字段信息

查询外键字段信息

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');