修改自增属性为非自增

妖狐艹你老母 2022-08-20 03:25 224阅读 0赞

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