查询关联到一个父表的所有子表字段
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from dba_constraints where r_constraint_name in (select constraint_name from dba_constraints where table_name='HEALTH_FEE')
order by owner,constraint_name;
select a.* from dba_cons_columns a,
(
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from dba_constraints where r_constraint_name in (select constraint_name from dba_constraints where table_name='STAFF_INFO')) b
where a.table_name=b.table_name
and a.constraint_name=b.constraint_name
and a.column_name like '%FCU%'
order by a.owner,a.table_name,a.constraint_name;
---查找存在外键的字段列
select a.owner,a.table_name,a.constraint_name,a.column_name from dba_cons_columns a,
(
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from dba_constraints where r_constraint_name in (select constraint_name from dba_constraints where table_name in ('BANK_TBL'))
) b
where a.table_name=b.table_name
and a.constraint_name=b.constraint_name
order by a.owner,a.table_name,a.constraint_name;
---查找存在外键的表和约束名
select a.owner,a.constraint_name,a.table_name,
a.r_owner,a.r_constraint_name from dba_constraints a where a.constraint_type='R' and wner='NETS2DATA'
and exists (select constraint_name
from dba_constraints b where b.owner='NETS2DATA' and a.r_constraint_name=b.constraint_name
and (b.constraint_type='P' or
b.constraint_type='U'));
---查找存在外键的字段列
select a.owner,a.table_name,a.constraint_name,a.column_name from dba_cons_columns a,
(
select a.owner,a.constraint_name,a.table_name,
a.r_owner,a.r_constraint_name from dba_constraints a where a.constraint_type='R' and wner='NETS2DATA'
and exists (select constraint_name
from dba_constraints b where b.owner='NETS2DATA' and a.r_constraint_name=b.constraint_name
and (b.constraint_type='P' or
b.constraint_type='U'))
) b
where a.owner=b.owner
and a.table_name=b.table_name
and a.constraint_name=b.constraint_name
order by a.owner,a.table_name,a.constraint_name;
---查询没有建立外键索引的字段信息
select * from (select a.owner,a.table_name,a.constraint_name,a.column_name from dba_cons_columns a,
(
select a.owner,a.constraint_name,a.table_name,
a.r_owner,a.r_constraint_name from dba_constraints a where a.constraint_type='R' and wner='NETS2DATA'
and exists (select constraint_name
from dba_constraints b where b.owner='NETS2DATA' and a.r_constraint_name=b.constraint_name
and (b.constraint_type='P' or
b.constraint_type='U'))
) b
where a.owner=b.owner
and a.table_name=b.table_name
and a.constraint_name=b.constraint_name ) tbl
where not exists(select 'X' from dba_ind_columns idx
where tbl.owner=idx.table_owner
and tbl.table_name=idx.table_name
and tbl.column_name=idx.column_name
and idx.column_position=1);
类别: dba脚本 查看评论Link URL: http://hi.baidu.com/ljm0211/blog/item/2d03a0443a577c99b3b7dc39.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11411056/viewspace-732755/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11411056/viewspace-732755/