ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 查询外键约束、子表字段等信息的SQL

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

原创 Linux操作系统 作者:ljm0211 时间:2012-07-02 14:49:08 0 删除 编辑

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

 

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);

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

请登录后发表评论 登录
全部评论

注册时间:2009-05-14

  • 博文量
    272
  • 访问量
    437145