To Find joins between Tables in Oracle Apps

To Find Joins between tables in oracle apps


set pagesize 5000
set linesize 5000
spool C:\Script\OUTPUT.TXT
set colsep "|"
select * from dual ;
set colsep " "
spool off
set pagesize 50
set linesize 200


/* Formatted on 2011/02/18 10:05 (Formatter Plus v4.8.8) */
SELECT   d.table_name "Table name", d.constraint_name "Constraint name",
         DECODE (d.constraint_type,
                 'P', 'Primary Key',
                 'R', 'Foreign Key',
                 'C', 'Check/Not Null',
                 'U', 'Unique',
                 'V', 'View Cons'
                ) "Type",
         d.search_condition "Check Condition", p.table_name "Ref Table name",
         p.constraint_name "Ref by", m.column_name "Ref col",
         m.POSITION "Position", p.owner "Ref owner"
    FROM dba_constraints d LEFT JOIN dba_constraints p
         ON (d.r_owner = p.owner AND d.r_constraint_name = p.constraint_name)
         LEFT JOIN dba_cons_columns m ON (d.constraint_name =
                                                             m.constraint_name
                                         )
   WHERE d.table_name IN (
                        SELECT table_name
                          FROM dba_tables
                         WHERE owner = UPPER ('mkm')
                        UNION ALL
                        SELECT view_name
                          FROM dba_views
                         WHERE owner = UPPER ('mkm'))
ORDER BY 1, 2