0 votes
1 view
in SQL by (22.4k points)

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?

1 Answer

0 votes
by (40.3k points)

You need to execute a query either manually or you can use other tools as for example PLSQL Developer can execute a query by using the development tool. 

Below SQL query can be 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

In the above query r_owner is the schema, and r_table_name is the table for which we are looking for references.

Note: Here, all the names are case sensitive.

But, Oracle SQL Developers don't have the option to find which table reference a given table is having.

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...