Selecting all foreign key references on table

Question

I need to select all foreign key references on a table. I came up with a query that works, but takes 9 seconds to execute. It would be very nice if someone could help with optimizing it. Thank you very much!

Here is the query:

SELECT A.TABLE_NAME REFENCING_TABLE,
       A.COLUMN_NAME REFENCING_COLUMN,
       B.TABLE_NAME REFERENCED_TABLE,
       B.COLUMN_NAME REFERENCED_COLUMN
  from (select TABLE_NAME,
               COLUMN_NAME,
               REFERENCED_CONSTRAINT_NAME,
               POSITION
          from all_cons_columns C
          join (select CONSTRAINT_NAME,
                       PK.REFERENCED_CONSTRAINT_NAME
                  from all_constraints FK
                  JOIN (select CONSTRAINT_NAME REFERENCED_CONSTRAINT_NAME 
                          from all_constraints
                          where constraint_type in ('P','U') and
                                table_name = :tablename) PK
                    on FK.R_CONSTRAINT_NAME = PK.REFERENCED_CONSTRAINT_NAME
                  where constraint_type = 'R') FK
            on C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME) A
  join (select TABLE_NAME,
               COLUMN_NAME,
               CONSTRAINT_NAME,
               POSITION
          from all_cons_columns) B
    on REFERENCED_CONSTRAINT_NAME = B.CONSTRAINT_NAME AND 
       A.POSITION = B.POSITION;

Do you need to do this across schemas? If you only need to do this in a single schema, change the `all_something` views to `user_something` views. Then - you take a table name as input. If you must do this across schemas, you should also input a schema name; you may have tables with the same name in different schemas, which may lead to confusion and incorrect results. — , Jan 13 '17 at 00:04

score 1 · Accepted Answer · answered Jan 13 '17 at 01:34

You may use this query to find all foreign key references on a table:

SELECT 
fk.owner fk_schema_owner,fk.table_name fk_table,
fk.column_name fk_column, fk.constraint_name fk_constraint_name,  
pk.r_owner pk_schema_owner, 
c_pk.table_name pk_table_name, c_pk.constraint_name pk_constraint_name
FROM 
all_cons_columns fk 
  JOIN all_constraints pk 
     ON fk.owner = pk.owner AND fk.constraint_name = pk.constraint_name
  JOIN all_constraints c_pk 
     ON pk.r_owner = c_pk.owner AND pk.r_constraint_name = c_pk.constraint_name
  WHERE pk.constraint_type = 'R' AND fk.table_name = :InputTableName

Selecting all foreign key references on table

1 Answers1