Select all references to a given table (Foreign key)

Question

I need a way to select all the Foreign key of a particular pk. In other words, I want to catch a determined Primary key and select all the Foreign Keys that "point" to her.

Preferably I want to get the name of spine which has the key, but can also be the same key name.

Answer 1

You can use the following select

select  r.owner, 
        r.table_name as tabela, 
        r.constraint_name as nome_chave,
        u.COLUMN_NAME
from  user_constraints r, 
      user_constraints o,
      user_cons_columns u
      where 
     r.r_owner = o.owner and 
     u.CONSTRAINT_NAME = r.CONSTRAINT_NAME and
      r.r_constraint_name = o.constraint_name and 
      o.constraint_type in ('P','U') and 
      r.constraint_type = 'R' and 
      o.table_name = '&NOME_DA_SUA_TABELA'

Based In that Article

Answer 2

PLSQL Developer implements the following query to obtain the list of references:

SELECT 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 

:r_owner is mentioned because in Oracle schemadifferent s may have different tables that have the same name.

Source:
https://stackoverflow.com/questions/1143728/how-can-i-find-which-tables-reference-a-given-table-in-oracle-sql-developer