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

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'