How to Find Which Tables Reference a Given Table in Oracle SQL Developer

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

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.

How can I find out which table a foreign key references?

You say you're using SQL Developer.

So, just open the table.

Go to the constraints page.

Find your foreign key.

Look at the R_TABLE_NAME column.

List of foreign keys and the tables they reference in Oracle DB

The referenced primary key is described in the columns r_owner and r_constraint_name of the table ALL_CONSTRAINTS. This will give you the info you want:

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
       -- referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R'
   AND a.table_name = :TableName

How to check which field in a table a foreign key references

Open Constraints tab. When you click one of the foreign key constraint name, columns wihch is used in the foreign key references will be seen in the Columns view at the bottom of Constraints Tab

Find table details in Oracle

If you are logged in as a DBA user, you can use:

SELECT *
FROM   dba_tab_columns
WHERE  OWNER = 'TEST'
AND    SUBSTR(TABLE_NAME, 1, 1) IN ('A', 'B', 'C', 'a', 'b', 'c');

Or you can query the all_tab_columns data dictionary view or, if you are logged in as the TEST user:

SELECT *
FROM   user_tab_columns
WHERE  SUBSTR(TABLE_NAME, 1, 1) IN ('A', 'B', 'C', 'a', 'b', 'c');

Oracle : which SQL command to get all details about a table?

I do something similar. I read those things from a SQL Server over OPENQUERY statements directly from Oracle DBs and save results into SQL Server tables to allow analysis of historic comparison schema information and changes.

So what you have to do with the resultsets of the following queries is to store them (regulary) somehow and add some kind of unique / primary key or timestamp to it, in order to distinguish between your different scans.

Leaving away the SQL Server specific code stuff, those are the basic oracle sql queries I use so far:

--Tables
SELECT table_name, owner, Tablespace_name, Num_Rows 
FROM all_tables WHERE tablespace_name is not NULL 
AND owner not in ('SYS', 'SYSTEM') 
ORDER BY  owner, table_name;

--Columns
SLECT OWNER, TABLE_NAME, Column_name, Data_type, data_length, data_precision, NULLABLE, character_Set_Name  
From all_tab_cols 
where USER_GENERATED = 'YES' 
AND owner not in ('SYS', 'SYSTEM');

--Indexes
select Owner, index_name, table_name, uniqueness,BLEVEL,STATUS from ALL_INDEXES
WHERE owner not in ('SYS', 'SYSTEM') 

--Constraints
select owner, constraint_name, constraint_type, table_name, search_condition, status, index_name, index_owner 
From all_constraints 
WHERE generated = 'USER NAME'
AND owner not in ('SYS', 'SYSTEM') 

--Role Previleges
select grantee, granted_role, admin_option, delegate_option, default_role, common
From DBA_ROLE_PRIVS

--Sys Privileges
select grantee, privilege, admin_option, common
From DBA_SYS_PRIVS 

How to find tables having foreign key to a table in Oracle?

SELECT d.table_name,

       d.constraint_name "Primary Constraint Name",

       b.constraint_name "Referenced Constraint Name"

FROM user_constraints d,

     (SELECT c.constraint_name,

             c.r_constraint_name,

             c.table_name

      FROM user_constraints c 

      WHERE table_name='EMPLOYEES' --your table name instead of EMPLOYEES

      AND constraint_type='R') b

WHERE d.constraint_name=b.r_constraint_name

Find all other tables that use a current table (oracle database)

You can use the data dictionaries to do this, but which data dictionary to use depends on the type of object in question. Because you want to find several different types of objects, you'll need to use several dictionaries, and, if you want the result all in one query, you will have to connect a few queries together via unions.

The following query (2 queries connected into one by a union) will show you tables with keys referencing table xyz in schema xyz, and also the names of triggers using table xyz in schema xyz (replace table and schema name with whatever you're searching on:

select 'Table has key referencing this table' as match_type, table_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 = 'TABLE_XYZ'
           and owner = 'SCHEMA_XYZ')
union all
select distinct 'Table is used by this trigger', trigger_name
  from all_triggers
 where table_name = 'TABLE_XYZ'
   and owner = 'SCHEMA_XYZ'

For other types of objects, use the same method with the next appropriate dictionary, and connect by a union. You will find this helpful:

http://www.oracle.com/pls/tahiti/tahiti.catalog_views

Note that I use the all_ dictionaries in the query above. Each has an equivalent dba_ and user_ dictionary. The all_ ones act as a dictionary for all objects to which the currently logged in user (you) has access. The dba_ one acts as a dictionary for all objects in the database, but you need to have the privilege to use the dba_ dictionaries, so I didn't use them in the above query (if you do have access, replace all_ with dba_). The user_ dictionaries act as a dictionary for all objects owned by the current user/schema.

---

Related Topics