oracle 数据库查看外键引用关系

￡神魔★判官ぃ 2022-12-24 02:59 116阅读 0赞

# 整体外键引用关系 #

select df.owner                   as child_table_owner, 
           df.table_name              as child_table_name, 
           df.constraint_name         as referenced_constraint_name,
           dc.owner                   as parent_table_owner, 
           dc.table_name              as parent_table_name, 
           dc.constraint_name         as parent_constraint_name
    from   all_constraints dc, 
           (select c.owner, 
                   c.constraint_name, 
                   c.r_constraint_name, 
                   c.table_name 
              from all_constraints c 
             where 1=1
               and constraint_type = 'R' ) df 
    where  1=1
      and dc.constraint_name =df.r_constraint_name 
      and dc.owner not in ('SYSTEM', 'SYS', 'DBSNMP', 'EXFSYS', 
                           'ORDDATA', 'CTXSYS', 'OLAPSYS', 'MDSYS', 'SYSMAN',
                           'DVSYS','APEX_040200', 'LBACSYS','FLOWS_FILES','GSMADMIN_INTERNAL' )
    ;

# A表引用别的表 #

select dc.owner as source_owner,
           dc.table_name as source_table_name,
           dcc.column_name as source_column_name,
           '||' as flag1,
           dc.constraint_name as source_constraint_name,
           dc.constraint_type as source_constraint_type,
           '||' as flag2,
           dc.r_owner as refer_owner,
           (select ui.table_name
              from user_indexes ui
             where 1 = 1
               and ui.index_name = dc.r_constraint_name) as refer_table_name,
           (select listagg(uic.COLUMN_NAME, ',') within group(order by uic.COLUMN_POSITION)
              from user_ind_columns uic
             where 1 = 1
               and uic.INDEX_NAME = dc.r_constraint_name) as refer_table_col,
           dc.r_constraint_name as refer_constraint_nam,
           '||' as flag3
    from user_constraints dc, 
         user_cons_columns dcc
    where 1 = 1
      and dc.owner = dcc.owner
      and dc.table_name = dcc.table_name
      and dc.constraint_name = dcc.constraint_name
      and dcc.table_name = 'REPAIR_BALANCE_ORDER'
      and dc.constraint_type = 'R' -- 外键引用
    order by dc.owner, dc.table_name, dcc.column_name
    ;

# 别的表引用A表 #

select dc.owner as source_owner,
           dc.table_name as source_table_name,
           dcc.column_name as source_column_name,
           '||' as flag1,
           dc.constraint_name as source_constraint_name,
           dc.constraint_type as source_constraint_type,
           '||' as flag2,
           dc.r_owner as refer_,
           (select ui.table_name
              from user_indexes ui
             where 1 = 1
               and ui.index_name = dc.r_constraint_name) as refer_table_name,
           (select listagg(uic.COLUMN_NAME, ',') within group(order by uic.COLUMN_POSITION)
              from user_ind_columns uic
             where 1 = 1
               and uic.INDEX_NAME = dc.r_constraint_name) as refer_table_col,
           dc.r_constraint_name as refer_constraint_name,
          '||' as flag3
    from user_constraints dc, 
         user_cons_columns dcc
    where 1 = 1
      and dc.owner = dcc.owner
      and dc.table_name = dcc.table_name
      and dc.constraint_name = dcc.constraint_name
      and dc.r_constraint_name in (
          select ui.constraint_name
            from user_constraints ui
           where 1 = 1
             and ui.table_name = 'STATUS_ITEM')
      and dc.constraint_type = 'R' -- 外键引用
    order by dc.owner, dc.table_name, dcc.column_name
    ;