我有一个学生表,很简单
s_id (pk) s_name
1 Mr. x
2 Mr. y
s_id 使用表格
类
c_id(pk), s_id(fk) c1 1 c2 1
图书馆
l_id(pk) s_id(fk) l1 2 l2 1
现在我的问题是,当我在这段时间内删除学生s_id时,我想检查这 1 (s_id) 是否在课堂、图书馆或许多其他表中用作 fk
我想通过table_name、key_name和key_value进行检查(例如学生、s_id、1)
查询将基于使用此键检查所有引用表简单 true 或 false 来返回。
谢谢
使用此查询,应用所需的筛选器:
select
PKTABLE_NAME = convert(sysname,o1.name),
PKCOLUMN_NAME = convert(sysname,c1.name),
FKTABLE_NAME = convert(sysname,o2.name),
FKCOLUMN_NAME = convert(sysname,c2.name),
FK_NAME = convert(sysname,object_name(f.object_id)),
PK_NAME = convert(sysname,i.name)
from
sys.objects o1,
sys.objects o2,
sys.columns c1,
sys.columns c2,
sys.foreign_keys f inner join
sys.foreign_key_columns k on (k.constraint_object_id = f.object_id) inner join
sys.indexes i on (f.referenced_object_id = i.object_id and f.key_index_id = i.index_id)
where
o1.object_id = f.referenced_object_id and
o2.object_id = f.parent_object_id and
c1.object_id = f.referenced_object_id and
c2.object_id = f.parent_object_id and
c1.column_id = k.referenced_column_id and
c2.column_id = k.parent_column_id
order by 1,2,3,4,5,6
您可以查看sp_fkeys
以获取更多详细信息