LIST ALL CHILDREN TABLES AND FK'S

ora_Lista_tabelas_filhas.sql

/* LIST ALL CHILDREN TABLES AND FK'S
                        	CREATED BY RAFAEL CAETANO PINTO   2007/08/15
**********************************************************************************/
SELECT
	UC.CONSTRAINT_NAME CONSTRAINT_NAME,
        UC.TABLE_NAME CHILD_TABLE,
        UCC.COLUMN_NAME CHILD_COLUMN,
        UCR.TABLE_NAME PARENT_TABLE,
        UCCR.COLUMN_NAME PARENT_COLUMN
FROM USER_CONSTRAINTS UC
INNER JOIN USER_CONSTRAINTS UCR ON UCR.CONSTRAINT_NAME = UC.R_CONSTRAINT_NAME
INNER JOIN USER_CONS_COLUMNS UCC ON  UCC.CONSTRAINT_NAME  = UC.CONSTRAINT_NAME AND UC.TABLE_NAME = UCC.TABLE_NAME
INNER JOIN USER_CONS_COLUMNS UCCR ON UCCR.CONSTRAINT_NAME = UCR.CONSTRAINT_NAME AND UCR.TABLE_NAME = UCCR.TABLE_NAME AND UCCR.POSITION = UCC.POSITION
WHERE UCR.TABLE_NAME IN ('&PARENT_TABLE') --PUT THE TABLE NAME HERE
AND UCR.CONSTRAINT_TYPE IN( 'P','U')
ORDER BY CHILD_TABLE, CONSTRAINT_NAME, CHILD_COLUMN