修改自增属性为非自增

该过程是因业务需要需要重构所有表结构--为实现取消表上的自增属性，索引结构保持不变而开发的一套脚本；

DECLARE @tablename SYSNAME
DECLARE @i INT=1
DECLARE @count INT
SELECT @count = Count(*)
FROM   sys.tables
WHERE  type = 'U'
       AND type_desc = 'USER_TABLE'WHILE @i<=@count
BEGIN
    SELECT @tablename=name FROM (SELECT Row_number() OVER(ORDER BY name )rn,name FROM sys.tables WHERE type='U' AND type_desc='USER_TABLE')a WHERE rn=@i
    EXEC Pro_alter_identity @tablename
    SET @i=@i+1
END

GO

IF EXISTS(SELECT name
          FROM   sysobjects
          WHERE  NAME = 'Pro_alter_identity'
                 AND type = 'P')
  DROP PROCEDURE PRO_ALTER_IDENTITY

GO

-- =============================================
-- Author:        zhaowenzhong
-- Create date: 2015.03.30
-- Description:    取消自增字段的自增属性，其他属性保持不变
-- =============================================
CREATE PROCEDURE Pro_alter_identity
(
  @TABLE SYSNAME='Feed_Test'
)
AS
  BEGIN
      SET NOCOUNT ON

      DECLARE @ERROR INT=0
      DECLARE @IDENT_COLUMN NVARCHAR(128)
      DECLARE @CONSTRAINT_NAME NVARCHAR(128)
      DECLARE @INDEX_KEYS NVARCHAR(512)
      DECLARE @I INT=1
      DECLARE @COUNT INT
      DECLARE @tmp_index_name NVARCHAR(512)
      DECLARE @tmp_index_keys NVARCHAR(1024)
      DECLARE @DEL_INDEX_SQL NVARCHAR(1280)
      DECLARE @CREATE_INDEX_SQL NVARCHAR(1280)
      DECLARE @ALTER_SQL NVARCHAR(1280)
      DECLARE @UPDATE_SQL NVARCHAR(1280)
      DECLARE @DROP_CONS_SQL NVARCHAR(1280)
      DECLARE @DROP_COLUMN_SQL NVARCHAR(1280)
      DECLARE @RENAME_COLUMN_SQL NVARCHAR(1280)
      DECLARE @ADD_CONSTRAINT_SQL NVARCHAR(1280)
      DECLARE @INDEX_INCLUDE_KEYS NVARCHAR( 512)
      ---查找具有自增字段的对应的约束（索引）
      SELECT @IDENT_COLUMN = a. name
      FROM   syscolumns a
             INNER JOIN sysobjects b
                     ON a.id = b.id
                        AND b.xtype = 'U'
                        AND b.name <> 'dtproperties'
      WHERE  b.name = @TABLE
             AND Columnproperty (a.id,
                                 a.name,
                                 'IsIdentity') = 1
        IF @IDENT_COLUMN IS NULL
        BEGIN
            RETURN;
        END

     ---临时存储表上索引属性的信息
      CREATE TABLE   #ALL_INDEX
        (
           ID INT IDENTITY ( 1, 1 ) NOT NULL PRIMARY KEY ,
           INDEX_NAME        VARCHAR( 512),
           INDEX_DESCRIPTION VARCHAR (512),
           INDEX_KEYS        VARCHAR( 512),
           INDEX_INCLUDE     VARCHAR( 512)
        )
      --获取表结构信息
      INSERT INTO #ALL_INDEX (INDEX_NAME, INDEX_DESCRIPTION,INDEX_KEYS )
      EXEC Sp_helpindex @TABLE
      ------- 获取包含索引的字段

       DECLARE @Include VARCHAR( 1000)
       DECLARE @xy        INT
       DECLARE @max_count INT
        DECLARE @Update_Include_SQL NVARCHAR( MAX)
        DECLARE @dbname sysname
            SET @dbname ='[' + Db_name () + ']'

      SELECT @xy = 1 ,
             @max_count = MAX(ID )
      FROM   #ALL_INDEX

      WHILE @xy <= @max_count
        BEGIN
            SET @Update_Include_SQL =N'
                          set @Include=null
                          select @Include=isnull(@Include+'','','''')+c.name
                                      from '
                      + @dbname
                      + '.sys.columns C join
                                                  (select column_id,index_column_id from '
                      + @dbname
                      + '.sys.index_columns
                                                              where object_id=(select object_id from '
                      + @dbname + '.sys.tables where name like '''
                      + @TABLE
                      + ''') and index_id=
                                                                          (select index_id from '
                      + @dbname + '.sys.indexes where name='''
                      + ( SELECT INDEX_NAME
                         FROM   #ALL_INDEX
                         WHERE  id = @xy)
                      + ''' AND  OBJECT_ID=OBJECT_ID(N''' + @TABLE
                      + '''))
                                                              and is_included_column=1) t on c.object_id=(select object_id from '
                      + @dbname + '.sys.tables where name like '''
                      + @TABLE
                      + ''') and c.Column_id=t.column_id
                                                  order by t.index_column_id'
            EXEC Sp_executesql @Update_Include_SQL, N'@Include varchar(1000) out',@Include out
            UPDATE #ALL_INDEX
            SET    Index_Include = Isnull ( @Include,
                                           '')
            WHERE  ID = @xy
            SET @xy =@xy + 1
        END

      --获取自增属性信息
      SELECT @CONSTRAINT_NAME = INDEX_NAME,
             @INDEX_KEYS = INDEX_KEYS
      FROM  (SELECT INDEX_NAME,
                    CASE RIGHT(INDEX_KEYS,
                               3)
                      WHEN '(-)' THEN LEFT(INDEX_KEYS,
                                           Len(INDEX_KEYS) - 3)
                      ELSE INDEX_KEYS
                    END INDEX_KEYS
             FROM   #ALL_INDEX
             WHERE  INDEX_DESCRIPTION LIKE 'clustered%'
                    AND INDEX_NAME = (SELECT b.name AS ConstraintName
                                      FROM   sys.sysconstraints AS a
                                             JOIN sys.key_constraints AS b
                                               ON a.constid = b.object_id
                                                  AND a.id = b.parent_object_id
                                      WHERE  a.id = Object_id(@TABLE)
                                             AND a.status = 2593))RES
      WHERE  INDEX_KEYS = @IDENT_COLUMN
              OR INDEX_KEYS LIKE '%' + @IDENT_COLUMN + '%' --复合主键

    IF @CONSTRAINT_NAME IS NULL
        BEGIN
            RETURN;
        END
     IF @INDEX_KEYS IS NULL
        BEGIN
            RETURN;
        END
  /*
      处理 自增字段存在多个约束或索引中的情况。
      先获取包含自增字段的索引索引，然后将这些索引写到 dba_maintenance.dbo.tmp_del_index表中。
      最后将这些索引先删除。当重名完自增字段 后再创建这些索引。
  */

    BEGIN TRANSACTION TRA_NAME

      ---将包含自增字段的 非聚集索引 信息备份写入到 维护库的对应表中
      INSERT INTO DBA_Maintenance.dbo.tmp_del_index
      SELECT Db_name(),
             @TABLE,
             INDEX_NAME,
             INDEX_KEYS,
             INDEX_INCLUDE
      FROM   #ALL_INDEX
      WHERE  INDEX_DESCRIPTION LIKE '%nonclustered%'

      SET @ERROR=@@ERROR
      ---根据维护库中的备份索引属性信息 删除表上对应的包含自增字段的索引
      SELECT @COUNT = Count(*)
      FROM   DBA_Maintenance.dbo.tmp_del_index
      WHERE  DBNAME = Db_name()
             AND TABLENAME = @TABLE
      WHILE @I <= @COUNT
        BEGIN
            SELECT @tmp_index_name = index_name
            FROM   (SELECT Row_number()
                             OVER(
                               ORDER BY index_name)rn,
                           index_name
                    FROM   DBA_Maintenance.dbo.tmp_del_index
                    WHERE  DBNAME = Db_name()
                           AND TABLENAME = @TABLE)tmp
            WHERE  rn = @I
            SET @DEL_INDEX_SQL=N'DROP INDEX ' + @tmp_index_name + ' ON '
                               + @TABLE
            EXEC (@DEL_INDEX_SQL)
            
            SET @ERROR=@@ERROR+@ERROR

            SET @I=@I + 1
        END

      IF @CONSTRAINT_NAME IS NOT NULL
        BEGIN
            SET @ALTER_SQL='ALTER TABLE ' + @TABLE
                           + ' ADD TmpID INT NOT NULL DEFAULT(0) ;'--增加一个临时字段
            SET @UPDATE_SQL= 'UPDATE  ' + @TABLE + ' SET TmpID='
                             + @IDENT_COLUMN + ''--将自增字段的值赋予临时字段
            SET @DROP_CONS_SQL='ALTER TABLE ' + @TABLE + ' DROP CONSTRAINT '
                               + @CONSTRAINT_NAME--删除自增字段上的约束
            SET @DROP_COLUMN_SQL='ALTER TABLE ' + @TABLE + ' DROP COLUMN '
                                 + @IDENT_COLUMN--删除自增字段
            SET @RENAME_COLUMN_SQL='EXEC SP_RENAME ''' + @TABLE + '.TmpID'','''
                                   + @IDENT_COLUMN + ''',''COLUMN'''--将临时字段重命名为原自增字段
           
            ---依据复合索引还是单自增字段索引来做相应的添回操作
            IF Charindex(',', @INDEX_KEYS) >= 1
              BEGIN
                  SET @ADD_CONSTRAINT_SQL='ALTER TABLE ' + @TABLE + '  ADD CONSTRAINT '
                                          + @CONSTRAINT_NAME
                                          + ' PRIMARY KEY CLUSTERED ( ' + @INDEX_KEYS
                                          + ')'---将原来的自增字段的约束添加回。
              END
            ELSE
              BEGIN
                  SET @ADD_CONSTRAINT_SQL='ALTER TABLE ' + @TABLE
                                          + '  ADD CONSTRAINT PK_' + @TABLE + '_'
                                          + @IDENT_COLUMN + ' PRIMARY KEY CLUSTERED ( '
                                          + @IDENT_COLUMN + ')'---将原来的自增字段的约束添加回。
              END
            --PRINT @ALTER_SQL
            --PRINT @UPDATE_SQL
            --PRINT @DROP_CONS_SQL
            --PRINT @DROP_COLUMN_SQL
            --PRINT @RENAME_COLUMN_SQL
            --PRINT @ADD_CONSTRAINT_SQL
            EXEC (@ALTER_SQL)
            SET @ERROR=@@ERROR+@ERROR
            EXEC (@UPDATE_SQL)
            SET @ERROR=@@ERROR+@ERROR
            EXEC (@DROP_CONS_SQL)
            SET @ERROR=@@ERROR+@ERROR
            EXEC (@DROP_COLUMN_SQL)
            SET @ERROR=@@ERROR+@ERROR
            EXEC (@RENAME_COLUMN_SQL)
            SET @ERROR=@@ERROR+@ERROR
            EXEC (@ADD_CONSTRAINT_SQL)
            SET @ERROR=@@ERROR+@ERROR

            ---- 最后 将被删除的 索引添加回去
            SET @I=1
            WHILE @I <= @COUNT
              BEGIN
                  SELECT @tmp_index_name = index_name,
                         @tmp_index_keys = index_keys,
                         @INDEX_INCLUDE_KEYS=index_include
                  FROM   (SELECT Row_number()
                                   OVER(
                                     ORDER BY index_name)rn,
                                 index_name,
                                 index_keys,
                                 index_include
                          FROM   DBA_Maintenance.dbo.tmp_del_index
                          WHERE  DBNAME = Db_name()
                                 AND TABLENAME = @TABLE)tmp
                  WHERE  rn = @I
                    IF @index_include_keys IS NOT NULL AND @index_include_keys<>''
                    BEGIN
                        SET @CREATE_INDEX_SQL=N'CREATE NONCLUSTERED INDEX '+char(9)+
                                        + @tmp_index_name +char(9)+ ' ON '+char(9)+@TABLE + '('
                                        + @tmp_index_keys + ')
                                        INCLUDE(' + @index_include_keys + ')'
                    END
                    ELSE
                    BEGIN
                        SET @CREATE_INDEX_SQL=N'CREATE NONCLUSTERED INDEX '+char(9)+
                                            + @tmp_index_name +char(9)+ ' ON '+char(9)+@TABLE + '('
                                            + @tmp_index_keys + ')'
                    END
                  EXEC (@CREATE_INDEX_SQL)

                  SET @ERROR=@@ERROR+@ERROR

                  SET @I=@I + 1
              END
        END
    IF @ERROR=0
    BEGIN
        COMMIT TRANSACTION TRA_NAME
    END
    IF @ERROR<>0
    BEGIN
        ROLLBACK TRANSACTION TRA_NAME
    END

      SET NOCOUNT OFF
  END
GO