rem -----------------------------------------------------------------------
rem Filename:   d2kkeys.sql
rem Purpose:    Designer: List primary and foreign key references per table
rem Date:       28-Apr-2000
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

break on TAB

col tab format a15
col col format a15
col fk_tab format a15
col fk_col format a15

SELECT AL2.NAME TAB,
       AL3.NAME COL,
       AL4.CONSTRAINT_TYPE,
       DECODE(AL7.EL_NAME,'.',' ',AL7.EL_NAME) FK_TAB,
       DECODE(AL5.EL_NAME,'.',' ',AL5.EL_NAME) FK_COL
FROM   CI_APPLICATION_SYSTEMS AL1,
       CI_TABLE_DEFINITIONS AL2,
       CI_COLUMNS AL3,
       CI_KEY_COMPONENTS AL4,
       SDD_ELEMENTS AL5,
       SDD_ELEMENTS AL6,
       SDD_ELEMENTS AL7
WHERE (AL4.CONSTRAINT_REFERENCE=AL6.EL_ID(+)
  AND AL5.EL_WITHIN_ID             = AL7.EL_ID(+)
  AND AL4.FOREIGN_COLUMN_REFERENCE = AL5.EL_ID(+)
  AND AL1.ID                       = AL2.APPLICATION_SYSTEM_OWNED_BY
  AND AL2.ID                       = AL3.TABLE_REFERENCE
  AND AL3.ID                       = AL4.COLUMN_REFERENCE
      )
  AND ((AL1.NAME                = 'FRANK'    -- Enter your app name here !!!
  AND   AL1.LATEST_VERSION_FLAG = 'Y'   
  AND  (NOT AL6.EL_OCCUR_TYPE(+)='FOREIGN')))
/