Thursday, October 11, 2012

To Find joins between Tables in Oracle Apps

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('po')
                         UNION ALL
                         SELECT view_name
                           FROM dba_views
                          WHERE owner = UPPER('po') )
ORDER BY 1
        ,2

Output :

No comments:

Post a Comment