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?

Winniewinnifred

To clarify the direction of references being asked here: This Question is asking (from the perspective of emp table) references pointing inward to emp (from other tables). If you want the other direction (outward references that TableName makes from itself [to other tables]), I think that is answered by this other question. – Tillfourd

Here is an actual UI-only Answer (that OP actually asks for in this Question). And separately, here is the [so far] simplest SQL Answer. – Tillfourd

Answer 1

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.

Piggin

Thanks for the answer. Shame on Oracle Sql Developer for sucking. – Merkley

You mentioned PLSQL Developer being able to do this function, can you explain how? – Ingrown

@Nicholas, In the object browser, select a table, right-click on a table and select "Foreign key References" – Piggin

This answer references that SQL Developer 4.1 and up now have an option the "Model" tab that will show this information in ERD format. – Nipple

r_owner is schema you are using, r_table_name is table which you are looking for references – Councilor

How can I make this query fast? This takes about 3 sec on my database. – Newmint

Answer 2

To add this to SQL Developer as an extension do the following:

1. Save the below code into an xml file (e.g. 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. 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

3. 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.

4. Reference

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

Eolanda

Do you know what the node name is for Packages? All of the xsd links I find on the web are no longer valid (as in Oracle removed them). – Aerophobia

I added a small change to your suggestion: and owner = user before the order by, such that if you have two instances of the same tables in two schemas you get only the references relevant to your schema – Harrell

I added this condition : and owner = :OBJECT_OWNER before and exists. – Civies

@M-Denis, in this case you might miss references from other schemas. – Mccusker

after applying this and running describe books; and select * from books;, it does not show the fk references tab on Oracle sql developer VM. – Eggnog

Answer 3

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

Dubose

I think constraint_type in ('P','U') is superfluous, because if the constraint_type of a constraint TOTO is 'R', then TOTO's r_constraint_name is of course the name of a constraint of type 'P' OR 'U' in the referenced table. There is no need to specify it. You are using an IN, so it is just like lots of OR and we only care about the only operand of OR which evaluates to true. – Anguilliform

Answer 4

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"
    );

Patronage

Foreign keys can reference Unique Keys, not just primary keys, also, the table name could be used in multiple schemas which would result in multiple matches. You need to use the 'Owner' column as well if you're going to use 'All_Constraints' and not 'User_Constraints'. – Instructive

Thanks for commenting what 'R' 'U' and 'P' are – Pygidium

Don't forget the semicolon at the end of the SQL request. – Anguilliform

By the way, constraint_type in ('P','U') is superfluous, because if the constraint_type of a constraint TOTO is 'R', then TOTO's r_constraint_name is of course the name of a constraint of type 'P' OR 'U' in the referenced table. There is no need to specify it. – Anguilliform

Answer 5

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.

Silverman

Less useful if you need this in a script for some reason, but if you just need to know about the connections this seems like the modern way to go. – Nipple

@Nipple well technically the question asks for a UI element so this is the most fitting answer – Apostolic

Answer 6

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.

Marginalia

Thanks. This helped me. – Cotidal

Answer 7

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; 

Hypochondrium

This is extremely useful - shows recursively all tables from a certain root table, which hold as a key a value of a column you select in this root table. Superb, thanks. – Edik

That is really cool - good work. I would only add lower() to compare table_name and column_name. – Berwick

This would be extremely useful for me too if my team actually used foreign keys in the DB layer. Sigh, back to looking at the hibernate code. – Aramaic

Answer 8

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 = <TABLE_OF_INTEREST>
   AND c.constraint_TYPE = 'R';

Depress

This worked for me when I changed the table name from dba_constraints to all_constraints like so: SELECT c.constraint_name, c.constraint_type, c2.constraint_name, c2.constraint_type, c2.table_name FROM all_constraints c JOIN all_constraints c2 ON (c.r_constraint_name = c2.constraint_name) WHERE c.table_name = '<TABLE_OF_INTEREST>' AND c.constraint_TYPE = 'R'; – Nishanishi

Answer 9

Only Replace table_name with your primary table name (in the case of OP Question, that would be emp)

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

Nic

unless I'm mistaken, and although this answer does not give attribution, this answer seems to be copied from this other Answer on a similar question. Although, I think, that Answer there would be more appropriate to have been on this page here; so it is nice that it is copied here (and it seems to be the simplest solution). – Tillfourd

Answer 10

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.

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

Equinox

Answer 11

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
;

Nissensohn

Answer 12

Replace MY_OWNER_NAME and MY_TABLE_NAME below and you are ready to go 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;

Goodin