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
/
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
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22558114/viewspace-1097799/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22558114/viewspace-1097799/