How can I find which tables reference a given table in Oracle SQL Developer

Question

In Oracle SQL Developer  if I m viewing the information on a table  I can view the constraints  which let me see the foreign keys  and thus which tables are referenced by this table   and I can view the dependencies to see what packages and such reference the table  But I m not sure how to find which tables reference the table   For example  say I m looking at the emp table  There is another table emp dept which captures which employees work in which departments  which references the emp table through emp id  the primary key of the emp table  Is there a way  through some UI element in the program  not through SQL  to find that the emp dept table references the emp table  without me having to know that the emp dept table exists

User · Answer

SQL Developer 4 1  released in May of 2015  added a Model tab which shows table foreign keys which refer to your table in an Entity Relationship Diagram format

User · Answer

To add this to SQL Developer as an extension do the following    Save the below code into an xml file  e g  fk ref xml      lt items gt       lt item type  editor  node  TableNode  vertical  true  gt       lt title gt  lt   CDATA FK References   gt  lt  title gt       lt query gt           lt sql gt               lt   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   gt           lt  sql gt       lt  query gt       lt  item gt   lt  items gt     Add the extension to SQL Developer     Tools   Preferences Database   User Defined Extensions Click  Add Row  button In Type choose  EDITOR   Location is where you saved the xml file above Click  Ok  then restart SQL Developer  Navigate to any table and you should now see an additional tab next to SQL one  labelled FK References  which displays the new FK information  Reference   http   www oracle com technetwork issue-archive 2007 07-jul o47sql-086233 html

User · Answer

I like to do this with a straight SQL query  rather than messing about with the SQL Developer application  Here s how I just did it  Best to read through this and understand what s going on  so you can tweak it to fit your needs    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

User · Answer

No  There is no such option available from Oracle SQL Developer    You have to execute a query by hand or use other tool  For instance PLSQL Developer has such option   The following SQL is that one used by PLSQL Developer   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   Where r owner is the schema  and r table name is the table for which you are looking for references  The names are case sensitive    Be careful because on the reports tab of Oracle SQL Developer there is the option   All tables   Dependencies  this is from ALL DEPENDENCIES which refers to   dependencies between procedures  packages  functions  package bodies  and triggers accessible to the current user  including dependencies on views created without any database links    Then  this report have no value for your question

User · Answer

Replace  Your TABLE  with emp in the query below  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

User · Answer

This has been in the product for years - although it wasn t in the product in 2011   But  simply click on the Model page   Make sure you are on at least version 4 0  released in 2013  to access this feature

User · Answer

To add to the above answer for sql developer plugin  using the below xml will help in getting the column associated with the foreign key        lt items gt           lt item type  editor  node  TableNode  vertical  true  gt           lt title gt  lt   CDATA FK References   gt  lt  title gt           lt query gt               lt sql gt                   lt   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   gt               lt  sql gt           lt  query gt           lt  item gt       lt  items gt

User · Answer

You may be able to query this from the ALL CONSTRAINTS view   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

User · Answer

How about something like this   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    lt TABLE OF INTEREST gt     AND c constraint TYPE    R

User · Answer

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     amp  amp tableName              AND column name     amp  amp columnName   CONNECT BY NOCYCLE PRIOR table name   r table name                           AND PRIOR column name   r column name

[sql] How can I find which tables reference a given table in Oracle SQL Developer?

Examples related to sql

Examples related to oracle

Examples related to foreign-keys

Examples related to oracle-sqldeveloper