查询一个主键表的所有外键表.sql

来源：互联网发布：舰队collection淘宝编辑：程序博客网时间：2024/05/21 22:27

SELECT AA.TABLE_NAME 外键表名称, AA.COLUMN_NAME 外键列名,
BB.TABLE_NAME 主键表名称, BB.COLUMN_NAME 主键列名
FROM (SELECT A.CONSTRAINT_NAME,
               B.TABLE_NAME,
               B.COLUMN_NAME,
               A.R_CONSTRAINT_NAME
          FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
         WHERE A.CONSTRAINT_TYPE = 'R'
           AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME) AA,
       (SELECT DISTINCT A.R_CONSTRAINT_NAME,
                        B.TABLE_NAME,
                        B.CONSTRAINT_NAME,
                        B.COLUMN_NAME
          FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
         WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
           AND A.CONSTRAINT_TYPE = 'R') BB
WHERE AA.R_CONSTRAINT_NAME = BB.R_CONSTRAINT_NAME

CREATE TABLE tab2 AS
SELECT DISTINCT A.R_CONSTRAINT_NAME,
                        B.TABLE_NAME,
                        B.CONSTRAINT_NAME,
                        B.COLUMN_NAME
          FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B
         WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME
           AND A.CONSTRAINT_TYPE = 'R'