查詢外來鍵約束、子表欄位等資訊的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