0

I am interested in finding all the parent table that have a reference to the current table this is the query i am using

select owner,constraint_name , constraint_type ,  table_name , 
from ALL_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R' AND OWNER = 'AAA' AND R_CONSTRAINT_NAME IN 

(SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINT 
WHERE CONSTRAINT_TYPE IN ('P' , 'U') AND TABLE_NAME = 'table_name'
and owner = 'AAA'
);

this will give you the detail of the referencing (parent table) yet not the columns involved making the relation

I want a query that will generate the columns name as well

k21Anaiyalate
  • 11
  • 4
  • Check this thread https://stackoverflow.com/questions/1729996/list-of-foreign-keys-and-the-tables-they-reference – astentx Oct 20 '20 at 10:56
  • I want to know the table that is referencing the my current table and the column value that is being used for the reference \ – k21Anaiyalate Oct 20 '20 at 14:52
  • When you find the referencing table and column, you can get all the values from that column. From your question it is hard to guess that you need a value, not a columns. – astentx Oct 20 '20 at 15:36
  • I am interested in knowing the column values that make up the relation – k21Anaiyalate Oct 21 '20 at 05:50
  • I've already answered: get parent column name and get all the values from it via another select. Since you have ref constraint there's no value that is not present in reference table. Oracle does not store all the values of all relationships because it does not need this informations to be stored. – astentx Oct 21 '20 at 09:44

0 Answers0