1 指定表約束查詢

column owner format a10
column table_name format a20
column column_name format a20
column constraint_name format a20
column constraint_type format a20

select
    a.owner,
    a.table_name,
    b.column_name,
    a.constraint_name,
    a.constraint_type
from
    user_constraints a,
    user_cons_columns b
where
    a.owner = b.owner and
    a.table_name = b.table_name and
    a.table_name = upper('&tbl')
/

------------------------------------------------------------

2 主外來鍵關係樹形查詢 (所有表或指定父表)

column lv format 999
column table_relation_tree format a70

with temp_constraints as
(
    select
        table_name,
        constraint_name pkey_constraint,
        null fkey_constraint,
        null r_constraint_name
    from user_constraints
    where
        constraint_type = 'P' or constraint_type = 'U'
    union all
    select
        a.table_name,
        a.constraint_name pkey_constraint,
        b.constraint_name fkey_constraint,
        b.r_constraint_name
    from
        user_constraints a,
        user_constraints b
    where
        a.table_name = b.table_name and
        (a.constraint_type = 'P' or a.constraint_type = 'U') and
        b.constraint_type = 'R'
)
select
    (level - 1) lv, rpad('-', (level-1)*5, '-') || table_name table_relation_tree
from temp_constraints
start with
    fkey_constraint is null and table_name = upper('&p_tbl')
connect by
    pkey_constraint <> r_constraint_name and
    prior pkey_constraint = r_constraint_name
order by 1, 2
/

------------------------------------------------------------

3 主鍵外來鍵層次關係查詢

column fk format a40
column pk format a40

select
    uc.constraint_name||' ('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' fk,
    ucc2.constraint_name||' ('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' pk
from
    user_constraints uc,
    user_cons_columns ucc1,
    user_cons_columns ucc2
where
    uc.constraint_name = ucc1.constraint_name and
    uc.r_constraint_name = ucc2.constraint_name and
    ucc1.POSITION = ucc2.POSITION and
    uc.constraint_type = 'R'
order by ucc1.TABLE_NAME, uc.constraint_name
/

------------------------------------------------------------

4 主鍵外來鍵層次關係查詢

column parent format a30
column child format a30
column level format 99

with v as
(
    select
        parent_table.table_name parent,
        child_table.table_name child
    from
        user_tables parent_table,
        user_constraints parent_constraint,
        user_constraints child_constraint,
        user_tables child_table
    where
        parent_table.table_name = parent_constraint.table_name and
        parent_constraint.constraint_type IN( 'P', 'U' ) and
        child_constraint.r_constraint_name = parent_constraint.constraint_name and
        child_constraint.constraint_type   = 'R' and
        child_table.table_name = child_constraint.table_name and
        child_table.table_name != parent_table.table_name
)
select level, parent, child
from (
    select * from v
)
start with parent = upper('&p_tbl')
connect by prior child = parent
order by 1, 2, 3
/

------------------------------------------------------------

5 帶有複合主外來鍵關係的查詢

create or replace function get_str(p_constraint_name varchar2)
    return varchar2
is
    l_column_name varchar2(4000);
begin
    for cur in
        (
            select column_name,position
            from user_cons_columns
            where constraint_name = p_constraint_name
            order by position
        ) loop
        if cur.position = 1 or cur.position is null then
            l_column_name := cur.column_name;
        else
            l_column_name := l_column_name||','||cur.column_name;
        end if;
    end loop;
return l_column_name;
end;
/

with v_pk as
(
    select distinct
        a.constraint_name pk_con,
        a.table_name pk_table,
        get_str(a.constraint_name) pk_col,
        a.owner pk_owner
    from
        user_constraints a,
        user_cons_columns b
    where
        (a.constraint_type = 'P' or a.constraint_type = 'U') and
        a.constraint_name = b.constraint_name and
        a.owner = b.owner
),
v_fk as
(
    select distinct
        c.constraint_name fk_con,
        c.table_name fk_table,
        get_str(c.constraint_name) fk_col,
        c.r_owner r_pk_owner,
        c.r_constraint_name r_pk_con,
        c.owner fk_owner
    from
        user_constraints c,
        user_cons_columns d
    where
        c.constraint_type = 'R' and
        c.constraint_name = d.constraint_name and
        c.owner = d.owner
)
select
    rpad(fk.fk_con,35,'.')||fk_table||'('||fk_col||')' fk_info,
    rpad(pk.pk_con,35,'.')||pk_table||'('||pk_col||')' pk_info
from
    (select * from v_pk) pk,
    (select * from v_fk) fk
where
    pk.pk_owner = fk.r_pk_owner and
    pk.pk_con=fk.r_pk_con
/

檢視主外來鍵關係

相關文章