sql列转标识列

--普通列转标识列，邹建

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)