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

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