静坐常思己过,闲谈莫论人非,能受苦乃为志士,肯吃亏不是痴人,敬君子方显有德,怕小人不算无能,退一步天高地阔,让三分心平气和,欲进步需思退步,若着手先虑放手,如得意不宜重往,凡做事应有余步。持黄金为珍贵,知安乐方值千金,事临头三思为妙,怒上心忍让最高。切勿贪意外之财,知足者人心常乐。若能以此去处事,一生安乐任逍遥。

去掉外键约束, 删除数据

作者:大鹏 发布于:2009-7-21 12:10 Tuesday 分类:Asp.Net 2.0

[code]
/* usage:
exec fk_switch tablename,0
delete tablename where fieldname = abc
-- truncate table tablename
exec fk_switch tablename,1
*/
create proc fk_switch @tablename varchar(20),@status bit
as
declare @fk varchar(50),@fktable varchar(20)
declare @s varchar(1000)
declare cur cursor for
select b.name as fkname,c.name as fktablename
from sysforeignkeys a
join sysobjects b on a.constid = b.id
join sysobjects c on a.fkeyid = c.id
join sysobjects d on a.rkeyid = d.id
where d.name = @tablename
open cur
fetch next from cur into @fk,@fktable
while @@fetch_status = 0
begin
if @status = 0
begin
  set @s = 'alter table +@fktable+ nocheck constraint + @fk'
  print @s
end
else
begin
  set @s = 'alter table +@fktable+ check constraint + @fk'
  print @s
end
exec(@s)
fetch next from cur into @fk,@fktable
end
close cur
deallocate cur
[/code]


标签: sqlserver

et_highlighter
发表评论 »本文目前尚无任何评论

发表评论

干净网络从你做起,切勿黏贴小广告