查询emp表上的所有约束的详细信息

select a.f_tbn as table_name, a.f_col as column_name, a.f_con as constraint_name,
a.f_con_type as constraint_type, a.ck as check_condition,
case when a.f_rcon is not null then b.p_name||'('||b.p_col||')'
when a.f_rcon is null then null
end as referenced_column
from
(select f.table_name f_tbn, c.column_name f_col,
f.CONSTRAINT_NAME f_con, f.CONSTRAINT_TYPE f_con_type,
f.SEARCH_CONDITION ck, f.r_constraint_name f_rcon
from user_constraints f, user_cons_columns c
where f.table_name=c.table_name and f.constraint_name=c.constraint_name
) a
left join
(
select p.table_name p_name, p.CONSTRAINT_NAME p_con, c2.column_name p_col
from user_constraints p, user_cons_columns c2
where p.table_name=c2.table_name
and p.CONSTRAINT_NAME=c2.constraint_name
) b
on a.f_rcon=b.p_con
where a.f_tbn='EMP'[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/37724/viewspace-1058382/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/37724/viewspace-1058382/