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

In-depth explanation and potential gotchas

While the given fast answer provides a quick solution, it's essential to understand and be aware of some key details or caveats.

Case sensitivity: The bane of your Oracle existence

The devil is in the details, or in this case, in the case sensitivity. Oracle identifiers default to uppercase, and a case insensitive search could be potentially catastrophic.

-- You've got case sensitivity in your SQL
WHERE table_name = 'Target_Table' -- No-go zone. Only works if 'Target_Table' was created without quotes.
WHERE table_name = 'TARGET_TABLE' -- All aboard! Correct for standard upper-casing.


Broadcasting your query: The ALL_CONSTRAINTS approach

To broaden the search and include referencing tables from all user schemes, you can tap into the ALL_CONSTRAINTS view.

-- Broadcasting LIVE from all tables
SELECT a.table_name AS Referencing_Table,
       concat('References ', c.r_table_name) AS Reference_Type
FROM all_cons_columns a
JOIN all_constraints c ON a.constraint_name = c.constraint_name
WHERE c.constraint_type = 'R'
  AND c.r_table_name = 'YOUR_TABLE'; -- Remember, 'YOUR_TABLE' is waiting for your actual table name. 


You know the drill, replace 'YOUR_TABLE' with your table name and you're good to go!

A deep dive into Oracle SQL Developer

Gaining a visual edge: The Model tab

In SQL Developer 4.1 and later, the Model tab gives a visual representation of table relationships. It's like having a picture book for your foreign key relationships. Fair warning, it may make you feel like a database artist!

Extending your reach with extensions

Sometimes everything you need is a little extra muscle. Extensions—like SQL Developer Data Modeler—can be your oracle in Oracle, offering in-depth ER diagrams and relationship visuals.

Tools > Preferences > Extensions > Add > Apply Changes
-- Beware: May give you super-database powers.

The misleading "All tables / Dependencies" report

Watch out for this stealthy misdirector—the "All tables / Dependencies" report mainly talks about dependencies within a table. It's like asking for directions to the station but getting a floor map of the train instead.

Ramping up your SQL querying

Linking foreign keys with unique or primary keys

Get more ammo under your belt with this query targeting unique or primary keys that tie up with foreign keys:

-- Do you believe in love at first constraint?
SELECT uc.table_name, uc.constraint_name
FROM all_constraints uc
WHERE uc.constraint_type IN ('U', 'P')
  AND EXISTS (
    SELECT 1 FROM all_constraints fc
    WHERE fc.r_constraint_name = uc.constraint_name
  );


Diving into cross-schema relationships

Working with multiple-user environments could require cross-schema referencing. Focus on links where the owner of the target table differs from the referencing table's owner:

-- The SQL for: "It's not you, it's your schema."
SELECT ac.owner AS referencing_owner,
       ac.table_name AS referencing_table,
       acc.column_name AS referencing_column,
       acc.position AS key_position,
       ac2.owner AS referenced_owner,
       ac2.table_name AS referenced_table
FROM all_cons_columns acc
JOIN all_constraints ac ON acc.owner = ac.owner
                        AND acc.constraint_name = ac.constraint_name
JOIN all_constraints ac2 ON ac.r_owner = ac2.owner
                         AND ac.r_constraint_name = ac2.constraint_name
WHERE ac2.table_name = 'YOUR_TABLE'
  AND ac.constraint_type = 'R';


Keeping it simple with the FK References tab

If you're a fan of the keep-it-simple philosophy and don't want to deal with extensions, just use the native FK References tab in Oracle SQL Developer.