In Oracle SQL , how can I find which tables reference a specific column (i.e have it as foreign key )?

Question

I was studying tis question - How can I find which tables reference a given table in Oracle SQL Developer? ,

And It showed some code to find which tables reference a specified table :

elect 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

So I'm trying to tailor the code to get the tables that refer to a specific column (here , PREPARER_ID ) ; this is what I tried so far :

select column_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 column_name = :r_column_name
   and owner = :r_owner
 )
ORDER BY column_name, constraint_name

This gives me an error :

ORA-00904: "COLUMN_NAME": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 103 Column: 8

Ok...and? That didn't work? What happened? – Mark J. Bobak Jun 10 '15 at 16:38 — Mark J. Bobak, Jun 10 '15 at 16:38

score 2 · Answer 1 · answered Jun 10 '15 at 16:58

To query based on the column you need to look at the all_cons_columns view, as well as all_constraints. This doesn't have to use a subquery but following your pattern:

select ac.table_name, acc.column_name, ac.constraint_name, ac.status, ac.owner
from all_constraints ac
join all_cons_columns acc
on acc.owner = ac.owner
and acc.constraint_name = ac.constraint_name
and acc.table_name = ac.table_name
where ac.r_owner = :r_owner
and ac.constraint_type = 'R'
and ac.r_constraint_name in
 (
   select ac2.constraint_name
   from all_constraints ac2
   join all_cons_columns acc2
   on acc2.owner = ac2.owner
   and acc2.constraint_name = ac2.constraint_name
   and acc2.table_name = ac2.table_name
   where ac2.constraint_type in ('P', 'U')
   and acc2.column_name = :r_column_name
   and ac2.owner = :r_owner
 )
ORDER BY ac.table_name, acc.column_name, acc.constraint_name;

With a sample set-up:

create table parent_table (preparer_id number primary key);
create table child_table (some_col number references parent_table(preparer_id));

And bind settings:

var r_column_name varchar2(30);
var r_owner varchar2(30);

begin
  :r_column_name := 'PREPARER_ID';
  :r_owner := user;
end;
/

That gets:

TABLE_NAME           COLUMN_NAME          CONSTRAINT_NAME      STATUS   OWNER    
-------------------- -------------------- -------------------- -------- ----------
CHILD_TABLE          SOME_COL             SYS_C00101337        ENABLED  MY_SCHEMA

In Oracle SQL , how can I find which tables reference a specific column (i.e have it as foreign key )?

1 Answers1