Oracle 根据主表查找定位主外键关系

最新推荐文章于 2024-07-24 20:40:24 发布

VincentQB

最新推荐文章于 2024-07-24 20:40:24 发布

阅读量1.5k

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/zwjzqqb/article/details/80712821

版权

Oracle 专栏收录该内容

41 篇文章 3 订阅

订阅专栏

使用SYS用户定位全库的主外键关系：

select f.OWNER || '.' || f.TABLE_NAME || '.' || f.COLUMN_NAME father_col,
       c.OWNER || '.' || c.TABLE_NAME || '.' || c.COLUMN_NAME children_col
  from dba_cons_columns f, dba_cons_columns c
 where (f.CONSTRAINT_NAME, c.CONSTRAINT_NAME) in
       (select child.R_CONSTRAINT_NAME father_cons_name,
               child.CONSTRAINT_NAME   children_cons_name
          from dba_constraints father, dba_constraints child
         where father.CONSTRAINT_TYPE in ('P', 'U')
           and father.CONSTRAINT_NAME = child.R_CONSTRAINT_NAME);

使用SYS用户定位某个SCHEMA的主外键关系：

select f.OWNER || '.' || f.TABLE_NAME || '.' || f.COLUMN_NAME father_col,
       c.OWNER || '.' || c.TABLE_NAME || '.' || c.COLUMN_NAME children_col
  from dba_cons_columns f, dba_cons_columns c
 where (f.CONSTRAINT_NAME, c.CONSTRAINT_NAME) in
       (select child.R_CONSTRAINT_NAME father_cons_name,
               child.CONSTRAINT_NAME   children_cons_name
          from dba_constraints father, dba_constraints child
         where father.CONSTRAINT_TYPE in ('P', 'U')
           and father.CONSTRAINT_NAME = child.R_CONSTRAINT_NAME
           and father.OWNER in ('XXXXX'));

使用SYS用户定位某个表的外键关系：

select f.OWNER || '.' || f.TABLE_NAME || '.' || f.COLUMN_NAME father_col,
       c.OWNER || '.' || c.TABLE_NAME || '.' || c.COLUMN_NAME children_col
  from dba_cons_columns f, dba_cons_columns c
 where (f.CONSTRAINT_NAME, c.CONSTRAINT_NAME) in
       (select child.R_CONSTRAINT_NAME father_cons_name,
               child.CONSTRAINT_NAME   children_cons_name
          from dba_constraints father, dba_constraints child
         where father.CONSTRAINT_TYPE in ('P', 'U')
           and father.CONSTRAINT_NAME = child.R_CONSTRAINT_NAME
           and father.OWNER in ('XXXXX')
           and father.table_name in ('XXXXX'));

使用SCHEMA定位自己的所有主外键关系：

select f.OWNER || '.' || f.TABLE_NAME || '.' || f.COLUMN_NAME father_col,
       c.OWNER || '.' || c.TABLE_NAME || '.' || c.COLUMN_NAME children_col
  from user_cons_columns f, user_cons_columns c
 where (f.CONSTRAINT_NAME, c.CONSTRAINT_NAME) in
       (select child.R_CONSTRAINT_NAME father_cons_name,
               child.CONSTRAINT_NAME   children_cons_name
          from user_constraints father, user_constraints child
         where father.CONSTRAINT_TYPE in ('P', 'U')
           and father.CONSTRAINT_NAME = child.R_CONSTRAINT_NAME);

使用SCHEMA定位自己的某个表的外键关系：

select f.OWNER || '.' || f.TABLE_NAME || '.' || f.COLUMN_NAME father_col,
       c.OWNER || '.' || c.TABLE_NAME || '.' || c.COLUMN_NAME children_col
  from user_cons_columns f, user_cons_columns c
 where (f.CONSTRAINT_NAME, c.CONSTRAINT_NAME) in
       (select child.R_CONSTRAINT_NAME father_cons_name,
               child.CONSTRAINT_NAME   children_cons_name
          from user_constraints father, user_constraints child
         where father.CONSTRAINT_TYPE in ('P', 'U')
           and father.CONSTRAINT_NAME = child.R_CONSTRAINT_NAME
           and father.table_name in ('XXXXX'));

[TOC]