MSSQL数据库将SQL动态将列设为自增长

君飞 2016/7/12 17:06:39 数据库 次阅读
 --普通列转标识列,君飞工作室Create PROC SetColToIDENTITY@TableName sysname, --要处理的表名@FieldName sysname, --要转换的列名,必须是整型数据字段@increment int=1 --标识值的增量ASIF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),NIsUserTable),0)=0BEGIN RAI…}
--普通列转标识列,君飞工作室
Create PROC SetColToIDENTITY
@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,@s3 nvarchar(4000)
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'')
----------------------------------
set @s3=''
SELECT @s3=@s3+N','+QUOTENAME(c.name)
FROM sysobjects o,syscolumns c
WHERE o.name=@TableName
    AND o.id=c.id and c.xtype<>189
ORDER BY c.colid
SELECT @s3=STUFF(@s3,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'('+@s3+N')
SELECT '+@s3+N' FROM '+@bkTableName+N'
SET IDENTITY_INSERT '+@TableName+N' OFF
DROP TABLE '+@bkTableName)

0
Copyright © 2007-2016 kinfar.net studio,All Rights Reserved.