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

i studying tis question - how can find tables reference given table in oracle sql developer? ,

and showed code find tables reference specified table :

elect table_name, constraint_name, status, owner all_constraints r_owner = :r_owner , constraint_type = 'r' , r_constraint_name in  (    select constraint_name all_constraints    constraint_type in ('p', 'u')    , table_name = :r_table_name    , owner = :r_owner  ) order table_name, constraint_name 

so i'm trying tailor code tables refer specific column (here , preparer_id ) ; tried far :

select column_name, constraint_name, status, owner all_constraints r_owner = :r_owner , constraint_type = 'r' , r_constraint_name in  (    select constraint_name all_constraints    constraint_type in ('p', 'u')    , column_name = :r_column_name    , owner = :r_owner  ) order column_name, constraint_name 

this gives me error :

ora-00904: "column_name": invalid identifier 00904. 00000 -  "%s: invalid identifier" *cause:     *action: error @ line: 103 column: 8 

to query based on column need @ the all_cons_columns view, all_constraints. doesn't have use subquery following pattern:

select ac.table_name, acc.column_name, ac.constraint_name, ac.status, ac.owner all_constraints ac join all_cons_columns acc on acc.owner = ac.owner , acc.constraint_name = ac.constraint_name , acc.table_name = ac.table_name ac.r_owner = :r_owner , ac.constraint_type = 'r' , ac.r_constraint_name in  (    select ac2.constraint_name    all_constraints ac2    join all_cons_columns acc2    on acc2.owner = ac2.owner    , acc2.constraint_name = ac2.constraint_name    , acc2.table_name = ac2.table_name    ac2.constraint_type in ('p', 'u')    , acc2.column_name = :r_column_name    , ac2.owner = :r_owner  ) order ac.table_name, acc.column_name, acc.constraint_name; 

with 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