239.普通列转换为标识列

左手的ㄟ右手 2023-12-27 07:45 254阅读 0赞

CREATE PROC p_ToIDENTITY
    @TableName sysname, --要处理的表名
    @FieldName sysname, --要转换的列名,必须是整型数据字段
    @increment int=1    --标识值的增量
    AS
    IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0
    BEGIN
    	RAISERROR('"%s" 必须是当前数据库中已经存在的用户表',12,16,@TableName)
    	RETURN
    END
    
    --标识列转换处理检查
    DECLARE @s1 nvarchar(1000),@s2 nvarchar(4000),@bkTableName sysname
    SELECT @s2='',@bkTableName=CAST(NEWID() as char(36)),
    	@s1=CASE
    		WHEN c.status=0x80 
    		THEN N'表"%s"中已经有标识列'+QUOTENAME(c.name,N'"')
    			+N',列"%s"不能再被转换成标识列'
    		WHEN t.name LIKE '%int' 
    		THEN N','+QUOTENAME(c.name)
    			+N'=IDENTITY('+QUOTENAME(t.name)
    			+N',1,'+CAST(@increment as varchar)+N')'
    		WHEN c.scale=0 AND t.name IN(N'decimal',N'numeric') 
    		THEN N','+QUOTENAME(c.name)
    			+N'=IDENTITY('+QUOTENAME(t.name)
    			+N'('+CAST(c.prec as varchar)+N')'
    			+N',1,'+CAST(@increment as varchar)+N')'
    		ELSE N'表"%s"中的列"%s"定义"'+QUOTENAME(t.name)
    			+CASE WHEN c.scale>0 
    				THEN N'('+CAST(c.prec as varchar)
    					+N','+CAST(c.scale as varchar)+N')'
    				ELSE N'' END
    			+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.name=@FieldName OR c.status=0x80)
    IF @@ROWCOUNT=0
    BEGIN
    	RAISERROR(N'表 "%s" 中无列 "%s"',12,16,@TableName,@FieldName)
    	RETURN
    END
    IF LEFT(@s1,1)<>','
    BEGIN
    	RAISERROR(@s1,12,16,@TableName,@FieldName)
    	RETURN
    END
    
    --生成要处理的列
    SELECT @s2=@s2+N','+QUOTENAME(c.name)
    FROM sysobjects o,syscolumns c
    WHERE o.name=@TableName
    	AND o.id=c.id
    ORDER BY c.colid
    SELECT @s1=REPLACE(@s2+N',',N','+QUOTENAME(@FieldName)+N',',@s1+N','),
    	@s1=SUBSTRING(@s1,2,LEN(@s1)-2),
    	@s2=STUFF(@s2,1,1,N'')
    
    --修改原表名
    EXEC sp_rename @TableName,@bkTableName
    
    --转换为标识列处理
    SELECT @TableName=QUOTENAME(@TableName),
    	@bkTableName=QUOTENAME(@bkTableName)
    EXEC('
    SELECT '+@s1+' INTO '+@TableName+N' 
    FROM '+@bkTableName+' WHERE 1=0
    SET IDENTITY_INSERT '+@TableName+N' ON
    INSERT '+@TableName+N'('+@s2+N')
    SELECT '+@s2+N' FROM '+@bkTableName+N'
    SET IDENTITY_INSERT '+@TableName+N' OFF
    DROP TABLE '+@bkTableName)