DECLARE c1 cursor for select 'SET IDENTITY_INSERT ['+name+'] + '] on; ' from sysobjects where xtype = 'F' open c1 declare @c1 varchar(8000) fetch next from c1 into @c1 while(@@fetch_status=0) begin exec(@c1) fetch next from c1 into @c1 end close c1 deallocate c1
比较省事了,但慎用!!! 自己先建个库测试 一下sp_configure 'allow update',1 reconfigure with override goupdate syscolumns set colstat=0 where colstat=1 and COLUMNPROPERTY(id,name,'IsIdentity')=1 go sp_configure 'allow update',0 reconfigure with override go
select 'SET IDENTITY_INSERT ['+name+'] + '] on; '
from sysobjects
where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next from c1 into @c1
end
close c1
deallocate c1
reconfigure with override
goupdate syscolumns set colstat=0 where colstat=1 and COLUMNPROPERTY(id,name,'IsIdentity')=1
go
sp_configure 'allow update',0
reconfigure with override
go
运行后提示:
配置选项 'allow updates' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。
消息 259,级别 16,状态 1,第 2 行
不允许对系统目录进行即席更新。
配置选项 'allow updates' 已从 1 更改为 0。请运行 RECONFIGURE 语句进行安装。这个是什么情况?
大师,这个第二行是不是有语法错误啊?帮忙再看看呗!没有注意一个会话中只能有一个SET IDENTITY_INSERT ...ON 所以这个办法是行不通的