如何在Oracle SQL Developer中找到哪些表引用给定表？

9 回答

• 1

  您可以从 ALL_CONSTRAINTS 视图中查询：

  SELECT table_name
  FROM ALL_CONSTRAINTS
  WHERE constraint_type = 'R' -- "Referential integrity"
    AND r_constraint_name IN
      ( SELECT constraint_name
        FROM ALL_CONSTRAINTS
        WHERE table_name = 'EMP'
          AND constraint_type IN ('U', 'P') -- "Unique" or "Primary key"
      );
  

  回复于 2024-04-29T23:56:41+08:00
• 7

  2015年5月发布的SQL Developer 4.1添加了一个Model选项卡，显示了以实体关系图格式引用表的表外键 .

  回复于 2024-04-29T23:56:41+08:00
• 3

  要添加sql developer插件的上述答案，使用下面的xml将有助于获取与外键关联的列 .

  <items>
          <item type="editor" node="TableNode" vertical="true">
          <title><![CDATA[FK References]]></title>
          <query>
              <sql>
                  <![CDATA[select a.owner,
                                  a.constraint_name,
                                  a.table_name,
                                  b.column_name,
                                  a.status
                           from   all_constraints a
                           join   all_cons_columns b ON b.constraint_name = a.constraint_name
                           where  a.constraint_type = 'R'
                                  and exists(
                                     select 1
                                     from   all_constraints
                                     where  constraint_name=a.r_constraint_name
                                            and constraint_type in ('P', 'U')
                                            and table_name = :OBJECT_NAME
                                            and owner = :OBJECT_OWNER)
                                     order by table_name, constraint_name]]>
              </sql>
          </query>
          </item>
      </items>
  

  回复于 2024-04-29T23:56:41+08:00
• 98

  这已经在产品中使用了多年 - 尽管它在2011年没有出现在产品中 .

  但是，只需单击“模型”页面即可 .

  确保您至少使用4.0版（2013年发布）才能访问此功能 .

  回复于 2024-04-29T23:56:41+08:00
• 33

  不可以.Oracle SQL Developer没有这样的选项 .

  您必须手动执行查询或使用其他工具（例如PLSQL Developer有此选项） . 以下SQL是PLSQL Developer使用的SQL：

  select table_name, constraint_name, status, owner
  from all_constraints
  where r_owner = :r_owner
  and constraint_type = 'R'
  and r_constraint_name in
   (
     select constraint_name from all_constraints
     where constraint_type in ('P', 'U')
     and table_name = :r_table_name
     and owner = :r_owner
   )
  order by table_name, constraint_name
  

  其中 r_owner 是架构， r_table_name 是您要查找其引用的表 . 名称区分大小写

  ---

  要小心，因为在Oracle SQL Developer的报告选项卡上有选项"All tables / Dependencies"这是来自ALL_DEPENDENCIES，它指的是“当前用户可访问的过程，包，函数，包体和触发器之间的依赖关系，包括对没有任何创建的视图的依赖性数据库链接 . “然后，此报告对您的问题没有任何 Value .

  回复于 2024-04-29T23:56:41+08:00
• 8

  在下面的查询中将[Your TABLE]替换为 emp

  select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
    from all_constraints 
   where constraint_type='R'
     and r_constraint_name in (select constraint_name 
                                 from all_constraints 
                                where constraint_type in ('P','U') 
                                  and table_name='[YOUR TABLE]');
  

  回复于 2024-04-29T23:56:41+08:00
• 0

  这样的事情怎么样：

  SELECT c.constraint_name, c.constraint_type, c2.constraint_name, c2.constraint_type, c2.table_name
    FROM dba_constraints c JOIN dba_constraints c2 ON (c.r_constraint_name = c2.constraint_name)
   WHERE c.table_name = <TABLE_OF_INTEREST>
     AND c.constraint_TYPE = 'R';
  

  回复于 2024-04-29T23:56:41+08:00
• 212

  SELECT DISTINCT table_name, 
                  constraint_name, 
                  column_name, 
                  r_table_name, 
                  position, 
                  constraint_type 
  FROM   (SELECT uc.table_name, 
                 uc.constraint_name, 
                 cols.column_name, 
                 (SELECT table_name 
                  FROM   user_constraints 
                  WHERE  constraint_name = uc.r_constraint_name) r_table_name, 
                 (SELECT column_name 
                  FROM   user_cons_columns 
                  WHERE  constraint_name = uc.r_constraint_name 
                         AND position = cols.position)           r_column_name, 
                 cols.position, 
                 uc.constraint_type 
          FROM   user_constraints uc 
                 inner join user_cons_columns cols 
                         ON uc.constraint_name = cols.constraint_name 
          WHERE  constraint_type != 'C') 
  START WITH table_name = '&&tableName' 
             AND column_name = '&&columnName' 
  CONNECT BY NOCYCLE PRIOR table_name = r_table_name 
                           AND PRIOR column_name = r_column_name;
  

  回复于 2024-04-29T23:56:41+08:00
• 4

  要将此作为扩展添加到SQL Developer，请执行以下操作：

  • 将以下代码保存到xml文件中（例如fk_ref.xml）：

  <items>
      <item type="editor" node="TableNode" vertical="true">
      <title><![CDATA[FK References]]></title>
      <query>
          <sql>
              <![CDATA[select a.owner,
                              a.table_name,
                              a.constraint_name,
                              a.status
                       from   all_constraints a
                       where  a.constraint_type = 'R'
                              and exists(
                                 select 1
                                 from   all_constraints
                                 where  constraint_name=a.r_constraint_name
                                        and constraint_type in ('P', 'U')
                                        and table_name = :OBJECT_NAME
                                        and owner = :OBJECT_OWNER)
                                 order by table_name, constraint_name]]>
          </sql>
      </query>
      </item>
  </items>
  

  • 将扩展添加到SQL Developer：

  • 工具>首选项

  • 数据库>用户定义的扩展

  • 单击"Add Row"按钮

  • 在类型中选择"EDITOR"，位置是您保存上面的xml文件的位置

  • 单击"Ok"然后重新启动SQL Developer

  • 导航到任何表，您现在应该看到SQL 1旁边的另一个选项卡，标记为FK References，它显示新的FK信息 .

  • 参考

  • http://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47sql-086233.html

  回复于 2024-04-29T23:56:41+08:00