How can I find which tables reference a given table in Oracle SQL Developer?
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?
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
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.
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):
<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>
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.
Read more... Read less...
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]');
You may be able to query this from the
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" );
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.
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.
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';