Tuesday, October 21, 2014

How to find tables having foreign key to a table in Oracle?

SELECT TABLE_NAME, CONSTRAINT_NAME,
STATUS, OWNER
FROM ALL_CONSTRAINTS
WHERE R_OWNER = 'SCOTT'
AND CONSTRAINT_TYPE = 'R'
AND R_CONSTRAINT_NAME IN
(
SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('P')
AND TABLE_NAME = 'EMP'
AND OWNER = 'SCOTT'
)
ORDER BY TABLE_NAME, CONSTRAINT_NAME;