See all the tables that have foreign keys to a certain column in a table?

Is this possible ? 开发者_StackOverflowIf yes, what are the tables involved, somewhere to look into .

Try something like this

select uc.table_name 
from sys.user_cons_columns ucc
join sys.user_constraints uc 
on uc.r_constraint_name = ucc.constraint_name
where constraint_type = 'R'
and ucc.table_name = :1
and ucc.column_name = :2

where

:1 = referenced table
:2 = referenced column

select * from user_constraints where r_constraint_name =<PK_NAME>

where <PK_NAME> is the name of the primary key constraint of the column

There are two candidate queries that might help here:

http://www.alberton.info/oracle_meta_info.html

SELECT alc.constraint_name,
          CASE alc.constraint_type
            WHEN 'P' THEN 'PRIMARY KEY'
            WHEN 'R' THEN 'FOREIGN KEY'
            WHEN 'U' THEN 'UNIQUE'
            WHEN 'C' THEN 'CHECK'
          END "constraint_type",
          alc.DELETE_RULE "on_delete",
          CASE alc.deferrable WHEN 'NOT DEFERRABLE' THEN 0 ELSE 1 END "deferrable",
          CASE alc.deferred WHEN 'IMMEDIATE' THEN 1 ELSE 0 END "initially_deferred",
          alc.search_condition,
          alc.table_name,
          cols.column_name,
          cols.position,
          r_alc.table_name "references_table",
          r_cols.column_name "references_field",
          r_cols.position "references_field_position"
     FROM all_cons_columns cols
LEFT JOIN all_constraints alc
       ON alc.constraint_name = cols.constraint_name
      AND alc.owner = cols.owner
LEFT JOIN all_constraints r_alc
       ON alc.r_constraint_name = r_alc.constraint_name
      AND alc.r_owner = r_alc.owner
LEFT JOIN all_cons_columns r_cols
       ON r_alc.constraint_name = r_cols.constraint_name
      AND r_alc.owner = r_cols.owner
      AND cols.position = r_cols.position
    WHERE alc.constraint_name = cols.constraint_name
      AND alc.constraint_name = 'TESTCONSTRAINTS_ID_FK'
      AND alc.table_name = 'TESTCONSTRAINTS2';