共享一个对标识列(Indntity)属性进行更改的存储过程，希望各位同仁提出宝贵意见，补充不足之处!

[kylike]

IF Exists (Select 1 From sysobjects Where name = 'SP_ConvertIdentity' and Xtype = 'P')
DROP PROC SP_ConvertIdentity
/*
使用举例：
SP_CovertIdentity 'Temp_0517','id',1,10,2
输入的参数依次是：表名、列字段、变更类型(1 更改为标识列，0 更改为非标识列)、起始种子、递增值.
如果变更类型为 0 , 可以忽略后面的两个参数。

*/
GO
CREATE PROC SP_ConvertIdentity --- SP_CovertIdentity 'Temp_0517','id',0,10,2
@TableName nvarchar(100), --- 表名
@Column nvarchar(100), --- 列名
@Type tinyint = 1 , --- 1 更改为标识列，0 更改为非标识列
@Begin int = 1 , --- 开始标识值
@Iden int = 1 --- 种子值
AS
/*
Author: like
createdate: 2005/5/17
Fucation: 改变标识列的字段属性。
Remark: 从整型转换为标识列时，会自动填充标识值，原有数据会被覆盖。
但从标识列转换为整型数据时，原有数据会保留。
*/
If Not Exists (Select 1 From sysobjects Where name = @TableName)
Begin
Raiserror ('不存在该表!',16,1)
Return
End

If Not Exists (Select 1 From syscolumns Where id = object_ID(@TableName) and Name = @Column)
Begin
Raiserror ('此列在表中不存在!',16,1)
Return
End

If Not Exists (Select 1 From syscolumns Where id = object_ID(@TableName) and Name = @Column And xtype in (56,48,127) )
Begin
Raiserror ('此列的数据类型不是整型!',16,1)
Return
End

If @Type not in (0,1)
Begin
Raiserror ('变更类型请输入0或1，1 更改为标识列，0更改为非标识列。',16,1)
Return
End

IF @Type = 0 and Not Exists (Select 1 From syscolumns Where ID = Object_id(@TableName) and Name = @Column and Status = 128)
Begin
Raiserror ('该列不是标识列，无须作任何更改!',16,1)
Return
End

If Exists (Select 1 From sysobjects a , syscolumns b ,sysindexkeys c
Where a.parent_obj = B.ID And a.parent_obj = C.ID And b.ID = C.ID
And a.xtype = 'PK' And b.ID=object_id(@tablename)
And b.Name = @Column and b.Colid = c.Colid )
Begin
Raiserror ('主键索引在引用该列，不能建标识列!',16,1)
Return
End

IF @Type = 1
BEGIN
If (Select cDefault From syscolumns Where id = object_ID(@TableName) and Name = @Column ) <> 0
Begin
Raiserror ('此列有默认值，不能建标识列!',16,1)
Return
End

If Exists (Select 1 From syscolumns Where id = object_ID(@TableName) and status = 128 )
Begin
Raiserror ('该表已经存在标识列，不能建标识列!',16,1)
Return
End

END


Declare @S nvarchar(2000),@S2 Nvarchar(1000),@Colid tinyint , @NewColumn nvarchar(150)
Set @NewColumn = 'Tem_'+@Column

Select @S = 'Alter Table ' + @TableName + ' Add ' + @NewColumn + ' '+
Case When @Type = 1 then Case When a.xtype = 48 Then 'tinyint' when a.xtype = 56 then 'Int' when a.xtype = 127 then 'bigint' end
+ ' not Null Identity('+Cast(@Begin as varchar(10))+','+Cast(@Iden as varchar(10)) + ')'
When @Type = 0 then Case When a.xtype = 48 Then ' tinyint Null ' when a.xtype = 56 then ' Int Null ' when a.xtype = 127 then ' bigint Null ' end
--- + Case When a.isnullable = 0 then ' Null' else ' Null' end
End , @Colid = Colid
From syscolumns a Where object_id(@TableName) = ID And Name = @Column

-- print @s
Exec (@s)

Select @S2 = Case when @Type = 1 Then ' Set Identity_insert '+@TableName+ ' On ' else ' ' end
+' Update '+ @TableName +' Set ' + @NewColumn + ' = ' + @Column
+ Case when @Type = 1 Then ' Set Identity_insert '+@TableName+ ' Off ' else
Case When a.isnullable = 0 then ' ALTER TABLE '+@TableName+ ' Alter Column ' + @NewColumn + ' ' +
Case When a.xtype = 48 Then 'tinyint Not Null' when a.xtype = 56 then 'Int Not Null' when a.xtype = 127 then 'bigint Not Null' end
Else ' ' end
End
From syscolumns a Where object_id(@TableName) = ID And Name = @Column

IF @Type = 0
-- print @S2
Exec (@S2)

Select @S2 = 'Alter Table ' + @TableName + ' Drop Column ' + @Column
-- print @S2
Exec (@S2)

Select @S2 = 'Exec sp_rename ''' + @TableName+'.['+@NewColumn + ']'' , ''' + @Column + ''', ''COLUMN'''
-- print @S2
Exec (@S2)


exec sp_configure 'allow updates',1 reconfigure with override

Select @S2 = 'Update Syscolumns Set Colid = '+ Cast(@Colid as varchar(10)) + ' Where ID = Object_id('''+ @TableName+ ''') and name = ''' + @Column+''''
-- print @S2
Exec (@S2)
exec sp_configure 'allow updates',0 reconfigure with override


GO

[kylike]

回贴给分了!

[iwl]

up