查询外键约束、子表字段等信息的SQL

查询关联到一个父表的所有子表字段

 

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/