PRIMARY KEYS and FOREIGN KEY of a TABLE

PRIMARY KEYS and FOREIGN KEY of a TABLE

column owner format a5
column r_owner format a5
column column_name format a12
column tt noprint
column position heading P format 9
column table_name format a15
column r_table_name format a15
column constraint_name format a15
column r_constraint_name format a15


select
        a.tt,
        a.owner,
        b.table_name,
        a.constraint_name,
        b.column_name,
        b.position,
        a.r_constraint_name,
        c.column_name,
        c.position,
        c.table_name r_table_name,
        a.r_owner
from
        (select
                owner,
                constraint_name,
                r_constraint_name,
                r_owner,1 tt
        from
                dba_constraints
        where
                owner=upper('&&owner')
                and table_name=upper('&&table_name')
                and constraint_type!='C'
        union
        select
                owner,
                constraint_name,
                r_constraint_name,
                r_owner,2
        from
                dba_constraints
        where
                (r_constraint_name,r_owner) in
                (select
                        constraint_name,
                        owner
                from
                        dba_constraints
                where
                        owner=upper('&owner')
                        and table_name=upper('&table_name'))
        ) a,
        dba_cons_columns b,
        dba_cons_columns c
where
        b.constraint_name=a.constraint_name
        and b.owner=a.owner
        and c.constraint_name=a.r_constraint_name
        and c.owner=a.r_owner
        and b.position=c.position
order   by 1,2,3,4,5;