如何实现对一个数据库(如:DataBase)所有表中的标识列(自增长)批量禁用和启用,因为一些原因要向DataBase库中的表插入数据,而且DataBase库中有标识列的表有上千个;一个个禁用启用的话,要疯的!
下面这段代码是在网上找到的,只是实现对于DataBase库中所有标识列的查询,但不知道怎么去批量禁用和启用标识列,哪位大师来化解下吧!都已经困扰好几天了!
小弟对SQL懂的不是很多,最好给点直接能用的代码和注释

USE TableName
GO
SELECT
表名=case when a.colorder=1 then d.name else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists
(
SELECT 1 FROM sysobjects where xtype='PK' and name in
(
SELECT name FROM sysindexes WHERE indid in
(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
)
)
)
then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√' else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')/*此列在绑定GridView时,如选择动态生成列,始终无法绑定.手动指定列后正常,原因不详*/
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
where COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1
------解决方案--------------------
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
go
update syscolumns set colstat=0 where colstat=1 and COLUMNPROPERTY(id,name,'IsIdentity')=1
go
sp_configure 'allow update',0
reconfigure with override
go