Script to find parent-child relationships between tables in oracle.

 Edit the below script and provide the inputs when executed 

********************************************************************

accept tablename prompt 'Enter the table name:'

accept tableowner prompt 'Enter the table owner name:'

SET LINESIZE 255

set verify off

col GIVEN_TABLE for a30

col PARENT_TABLE for a30

col CHILD_TABLE for a30

col CHILD_TABLE_OWNER for a20

col GIVEN_TABLE_OWNER for a20

col PARENT_TABLE_OWNER for a20

col PARENT_TAB_CONST for a30

col CHILD_TAB_FK_CONST for a30

col G_TAB_columns for a30

col GIVEN_FK_CONST_NAME for a30

col G_TAB_FK_COLUMNS for a30

col GIVEN_TAB_CONST for a30

Prompt Below are the Child tables for &tablename table.

prompt -----------------------------------------------

SELECT p.table_name GIVEN_TABLE, p.owner GIVEN_TABLE_OWNER, p.constraint_name GIVEN_TAB_CONST,

LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_columns

,c.owner CHILD_TABLE_OWNER,c.table_name CHILD_TABLE,c.constraint_name CHILD_TAB_FK_CONST

FROM dba_constraints p, dba_constraints c,dba_cons_columns t

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R'

AND p.constraint_name = c.r_constraint_name

and p.table_name=t.table_name

and p.constraint_name=t.constraint_name

AND p.table_name = UPPER('&tablename')

and p.owner=('&tableowner')

group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;

Prompt Below are the Parent tables for &tablename table.

prompt ------------------------------------------------

SELECT c.table_name GIVEN_TABLE,c.owner GIVEN_TABLE_OWNER, c.constraint_name GIVEN_FK_CONST_NAME,

LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_FK_columns,p.owner PARENT_TABLE_OWNER,p.table_name PARENT_TABLE

,p.constraint_name PARENT_TAB_CONST

FROM dba_constraints p, dba_constraints c ,dba_cons_columns t

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R'

AND p.constraint_name = c.r_constraint_name

and c.table_name=t.table_name

and c.constraint_name=t.constraint_name

AND c.table_name = UPPER('&tablename')

and c.owner='&tableowner'

group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;

***********************************************************