Hi, Is there a way we can find out the child table name from the Parent table name.I am able to find the parent table name from the child table name by using the below query but can we find the child table from the parent table?
SELECT ac.table_name,
column_name,
position,
ac.constraint_name,
Decode ( constraint_type, 'P', 'Primary Key',
'Foreign Key' ) key_type,
( SELECT ac2.table_name
FROM ALL_CONSTRAINTS ac2
WHERE ac2.constraint_name = ac.r_constraint_name ) fK_to_table
FROM ALL_CONS_COLUMNS acc,
ALL_CONSTRAINTS ac
WHERE acc.constraint_name = ac.constraint_name AND
acc.table_name = ac.table_name AND
constraint_type IN ( 'P', 'R' )
AND ac.table_name ='&TABLE_NAME'
ORDER BY table_name,constraint_type,position;
Enter value for table_name: EMPLOYEES
TABLE_NAME COLUMN_NAME POSITION CONSTRAINT_NAME KEY_TYPE FK_TO_TABLE
------------------------------ ------------------------------ ---------- ------------------------------ ----------- ------------------------------
EMPLOYEES EMPLOYEE_ID 1 EMP_EMP_ID_PK Primary Key
EMPLOYEES JOB_ID 1 EMP_JOB_FK Foreign Key JOBS
EMPLOYEES MANAGER_ID 1 EMP_MANAGER_FK Foreign Key EMPLOYEES
EMPLOYEES DEPARTMENT_ID 1 EMP_DEPT_FK Foreign Key DEPARTMENTS
4 rows selected.