如何查找哪些表引用了 Oracle SQL Developer 中的给定表？

Question

在 Oracle SQL Developer 中，如果我正在查看以下信息：一个表，我可以查看约束，这让我可以看到外键（以及该表引用了哪些表），并且我可以查看依赖项以查看哪些包等引用了该表。 但我不确定如何找到哪些表引用了该表。

例如，假设我正在查看 emp 表。 还有另一个表emp_dept，它捕获哪些员工在哪些部门工作，该表通过emp_id（emp_id）的主键引用emp表。 >emp 表。 有没有办法（通过程序中的某些 UI 元素，而不是通过 SQL）找到 emp_dept 表引用 emp 表，而无需我知道 < code>emp_dept 表存在吗？

Answer 1

像这样的事情怎么样：

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';

Answer 2

仅将 table_name 替换为您的主表名称（在 OP 问题的情况下，将是 emp）

select *
from all_constraints
where r_constraint_name in (
    select constraint_name
    from all_constraints
    where table_name='table_name'
); 

Answer 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>

Answer 4

我喜欢使用直接的 SQL 查询来完成此操作，而不是使用 SQL Developer 应用程序。

这就是我刚刚做的。 最好通读本文并了解发生了什么，这样您就可以调整它以满足您的需求......

WITH all_primary_keys AS (
  SELECT constraint_name AS pk_name,
         table_name
    FROM all_constraints
   WHERE owner = USER
     AND constraint_type = 'P'
)
  SELECT ac.table_name || ' table has a foreign key called ' || upper(ac.constraint_name)
         || ' which references the primary key ' || upper(ac.r_constraint_name) || ' on table ' || apk.table_name AS foreign_keys
    FROM all_constraints ac
         LEFT JOIN all_primary_keys apk
                ON ac.r_constraint_name = apk.pk_name
   WHERE ac.owner = USER
     AND ac.constraint_type = 'R'
     AND ac.table_name = nvl(upper(:table_name), ac.table_name)
ORDER BY ac.table_name, ac.constraint_name
;

Answer 5

替换下面的 MY_OWNER_NAME 和 MY_TABLE_NAME，然后您就可以RECURSIVELY了：

DECLARE
FUNCTION list_all_child_tables_and_constraints(asked_table_name in VARCHAR2, parent_table_name in VARCHAR2)
RETURN VARCHAR2 IS
    current_path VARCHAR2(100);
BEGIN
    FOR item IN
    (SELECT fk.TABLE_NAME, constraint_parent.FK FK1, constraint_child.FK FK2
        FROM all_constraints fk, all_constraints pk,
            (SELECT acc.CONSTRAINT_NAME, LISTAGG(acc.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY acc.COLUMN_NAME) AS FK
                FROM ALL_CONS_COLUMNS acc
                WHERE acc.OWNER = 'MY_OWNER_NAME'
                GROUP BY acc.CONSTRAINT_NAME) constraint_parent,
            (SELECT acc.CONSTRAINT_NAME, LISTAGG(acc.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY acc.COLUMN_NAME) AS FK
                FROM ALL_CONS_COLUMNS acc
                WHERE acc.OWNER = 'MY_OWNER_NAME'
                GROUP BY acc.CONSTRAINT_NAME) constraint_child
        WHERE pk.owner = fk.r_owner
            AND pk.constraint_name = fk.r_constraint_name
            AND fk.constraint_type = 'R'
            AND pk.table_name = asked_table_name
            AND constraint_parent.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
            AND constraint_child.CONSTRAINT_NAME = fk.R_CONSTRAINT_NAME
            AND pk.owner = 'MY_OWNER_NAME'
            AND fk.owner = 'MY_OWNER_NAME')
    LOOP
        current_path := parent_table_name || ' // ' || item.TABLE_NAME;
        DBMS_OUTPUT.PUT_LINE(current_path);
        DBMS_OUTPUT.PUT_LINE('     [' || item.FK1 || ']  [' || item.FK2 || ']');
        DBMS_OUTPUT.PUT_LINE('');
        current_path := list_all_child_tables_and_constraints(item.TABLE_NAME, current_path);

    END LOOP;
    RETURN '-----------FINISHED-----------';

EXCEPTION
    WHEN OTHERS THEN
        RETURN '-----------FINISHED-----------';
END list_all_child_tables_and_constraints;
BEGIN
DBMS_OUTPUT.PUT_LINE(list_all_child_tables_and_constraints('MY_TABLE_NAME', ''));
END;

Answer 6

不可以。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_DEPENDENCIES 指“当前用户可访问的过程、包、函数、包体和触发器之间的依赖关系，包括依赖于在没有任何数据库链接的情况下创建的视图。”。 那么，这份报告对你的问题没有任何价值。

Answer 7

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

1. 将以下代码保存到 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>

2. 将扩展添加到 SQL Developer：

   • 工具> 偏好设置
   • 数据库> 用户定义的扩展
   • 点击“添加行”按钮
   • 在“类型”中选择“编辑器”，位置是您保存上面 xml 文件的位置
   • 点击“确定”，然后重新启动 SQL Developer

3. 导航到任何表，您现在应该会在旁边看到一个附加选项卡SQL one，标记为 FK References，显示新的 FK 信息。

4. 参考

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

Answer 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]');

Answer 9

您可以从 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"
    );

Answer 10

SQL Developer 4.1 于 2015 年 5 月发布，添加了一个“模型”选项卡，该选项卡显示以实体关系图格式引用表的表外键。

Answer 11

该产品已存在多年 - 尽管 2011 年尚未出现在产品中。

但是，只需单击“型号”页面即可。

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

Answer 12

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;