因工作原因,需要修改sqlserver标识列的值。测试了多次,现总结如下:
一。exec sp_configure 'allow updates',1
reconfigure with override
二。update syscolumns set colstat = 0 where id = object_id(@tablename) and colstat = 1
三。在此修改相应标识列的内容
四。update syscolumns set colstat = 1 where id = object_id(@tablename) and name = @identitycolumn
五。exec sp_configure 'allow updates',0
reconfigure with override
六。删除默认约束
DECLARE @name varchar(100)
SELECT @name=b.name FROM syscolumns a,sysobjects b
WHERE a.id=object_id(表名) AND b.id=a.cdefault
AND a.name=列名
EXEC('alter table t_kl_zd_right drop constraint ' + @name)
go
alter table t_kl_zd_right alter column 列名 int
go