14.3.3 标识列转换为普通列.sql

sqlserver 数据库编程的绝好脚本

CREATE PROC p_DropIDENTITY
@TableName sysname --要处理的表名
AS
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0
BEGIN
	RAISERROR('"%s" 必须是当前数据库中已经存在的用户表',12,16,@TableName)
	RETURN
END

--标识列转换处理检查
DECLARE @s nvarchar(1000),@FieldName sysname,@bkFieldName sysname,@sql nvarchar(4000)
SELECT @FieldName=QUOTENAME(c.name),
	@bkFieldName=CAST(NEWID() as char(36)),
	@s=@FieldName+N' '+QUOTENAME(t.name)
		+CASE WHEN t.name LIKE '%int' THEN N''
		ELSE N'('+CAST(c.prec as varchar)
			+N','+CAST(c.scale as varchar)+N')'
		END
FROM sysobjects o,syscolumns c,systypes t
WHERE o.name=@TableName
	AND o.id=c.id
	AND c.xusertype=t.xusertype
	AND c.status=0x80
IF @@ROWCOUNT=0
BEGIN
	RAISERROR(N'表 "%s" 中无标识列',1,16,@TableName)
	RETURN
END

--修改标识列名
SET @sql=QUOTENAME(@TableName)+N'.'+@FieldName
EXEC sp_rename @sql,@bkFieldName,N'COLUMN'

--转换为标识列处理
SELECT @TableName=QUOTENAME(@TableName),
	@bkFieldName=QUOTENAME(@bkFieldName)
EXEC('ALTER TABLE '+@TableName+N' ADD '+@s)
EXEC('UPDATE '+@TableName+N' SET '+@FieldName+N'='+@bkFieldName+N'
ALTER TABLE '+@TableName+N' DROP COLUMN '+@bkFieldName)
RAISERROR(N'表 "%s" 中的标识列 "%s" 已经转换为普通列',1,16,@TableName,@FieldName)