在編寫S2SH代碼自動生成功能時需要用到,SQL代碼比較多做個日記,如下:
select b.table_name as pktable_name,b.column_name pkcolumn_name,
c.table_name fktable_name,c.column_name fkcolumn_name,c.position ke_seq,
c.constraint_name fk_name
from (select * from user_cons_columns ) b left join (select * from user_constraints
where user_constraints.constraint_type='R' ) a on b.constraint_name=a.r_constraint_name left join user_cons_columns c on c.constraint_name=a.constraint_name where c.position is not null and c.position=b.position order by c.table_name,c.position
上面是查詢目前使用者所有表主外鍵關系,如果要查詢某一個使用者加上使用者條件即可