需要破解foreign key约束的时候,需要知道foreign key的名称,sql server 2005中有两个试图可以用到:a) sys.foreign_key_columns b) sys.foreign_keys
用SQL查询当前库中的foreign key并连同引用表一起查询出来:
select cns.name,cns.object_id,cns.parent_object_id,cns.type_desc,
cns.referenced_object_id,obj.name
from sys.foreign_keys cns
left join sys.all_objects obj
on cns.referenced_object_id = obj.object_id ;
把主表,子表顺便一起查:
select cns.name,cns.object_id,cns.parent_object_id,par_obj.name as parent_object_name,cns.type_desc,
cns.referenced_object_id,obj.name
from sys.foreign_keys cns
left join sys.all_objects obj
on cns.referenced_object_id = obj.object_id
left join sys.all_objects par_obj
on cns.parent_object_id=par_obj.object_id
alter table capitalhistory
drop constraint R_1;