批量修改在索引中增加字段

╰+攻爆jí腚メ 2022-08-20 03:25 162阅读 0赞

前段时间由于业务结构重构，需要将DB中的所有索引中增加一个字段EI以满足重构后的业务需要；
    于是编写了该过程用以批量实现；
    
    ---------- AddOrUpdate End ----------
    if exists( select 1 from sys . procedures where name= 'PRO_Add_EI_INDEX')
    begin
                     DROP PROCEDURE PRO_Add_EI_INDEX
    end
    GO
    
    CREATE PROCEDURE PRO_Add_EI_INDEX
    (
      @TABLE SYSNAME='Feed_Test'
    )
    AS
    BEGIN
          SET NOCOUNT ON
    
    
          DECLARE @ERROR INT= 0
          DECLARE @Unique_Index_Constraint_Name NVARCHAR( 128)
          DECLARE @tmp_index_name NVARCHAR( 512)
          DECLARE @DEL_INDEX_SQL NVARCHAR( 1280)
          ---临时存储表上索引属性的信息
                   
          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_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_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_SQL, N'@Include varchar(1000) out',@Include out
                UPDATE #ALL_INDEX
                SET    Index_Include = Isnull ( @Include,
                                               '')
                WHERE  ID = @xy
                SET @xy =@xy + 1
            END
    
    ---将索引 信息备份写入到 维护库的对应表中
            INSERT INTO DBA_Maintenance. dbo. source_table_index
            SELECT Db_name (),
                @TABLE,
                INDEX_NAME,
                INDEX_DESCRIPTION,
                CASE RIGHT( INDEX_KEYS,
    )
                WHEN '(-)' THEN LEFT(INDEX_KEYS, Len(INDEX_KEYS ) - 3)
                ELSE INDEX_KEYS
                END INDEX_KEYS ,
                INDEX_INCLUDE
              FROM   #ALL_INDEX
                     --               ---将DB中所有约束备份保存到 维护库的对应表中
          INSERT INTO DBA_Maintenance. dbo. source_table_constraints select db_name(), object_name (parent_object_id), name from sys . key_constraints WHERE   parent_object_id=OBJECT_ID (N'' + @TABLE+'' )
    
          BEGIN TRANSACTION TRA_NAME
    
    ----删除唯一约束(包括主键唯一约束）
    
            DECLARE @Drop_Unique_Index_Sql nvarchar( 1280)
            DECLARE @J INT= 1
            DECLARE @JCOUNT INT= 0
            DECLARE @DROP_CONSTRAINT_SQL NVARCHAR( 3200)
    /*
            SELECT @JCOUNT=COUNT(*)
            FROM  [DBA_Maintenance].[dbo].[source_table_index] WITH(NOLOCK)
                WHERE DBNAME = Db_name()
                    AND TABLENAME = @TABLE
                    AND INDEX_DESCRIPTION  LIKE '%unique%'
    
        WHILE @J<=@JCOUNT
        BEGIN
            SELECT @Unique_Index_Constraint_Name=Index_Name
                FROM (SELECT Index_Name,row_number()over(order by Index_Name)rn
                        FROM [DBA_Maintenance].[dbo].[source_table_index] WITH(NOLOCK)
                            WHERE DBNAME = Db_name()
                            AND TABLENAME = @TABLE
                            AND INDEX_DESCRIPTION  LIKE '%unique%'
                      )AA
                 WHERE rn=@J
            SET @Drop_Unique_Index_Sql='ALTER TABLE [dbo].['+@TABLE+'] DROP CONSTRAINT ['+@Unique_Index_Constraint_Name+']'
    
            --print @@Drop_Unique_Index_Sql
            EXEC (@Drop_Unique_Index_Sql)
            SET @J=@J+1
    
        END
        */
    
        SELECT @JCOUNT =COUNT (*) FROM   dba_maintenance. dbo . source_table_constraints WITH(NOLOCK )
                        WHERE DBNAME = Db_name()
                        AND TABLENAME = @TABLE
    
            WHILE @J <=@JCOUNT
            BEGIN
                SELECT @Unique_Index_Constraint_Name =constraintsname
                        FROM (SELECT constraintsname, ROW_NUMBER() OVER( ORDER BY CONSTRAINTSNAME)RN
                                FROM  dba_maintenance .dbo .source_table_constraints WITH(NOLOCK )
                                       WHERE DBNAME = Db_name()
                                             AND TABLENAME = @TABLE
                                                                                                                     )AA
                         WHERE RN =@J
                SET @DROP_CONSTRAINT_SQL ='ALTER TABLE [dbo].[' + @TABLE +'] DROP CONSTRAINT ['+@Unique_Index_Constraint_Name+ ']'
                print 1
                print @DROP_CONSTRAINT_SQL
                EXEC (@DROP_CONSTRAINT_SQL )
                SET @J =@J + 1
            END
    
    
           DECLARE @I INT= 1
          DECLARE @COUNT INT= 0
          ---根据维护库中的备份索引属性信息 删除表上对应的非约束类索引
          SELECT @COUNT = Count(*)
          FROM   DBA_Maintenance .dbo . source_table_index   a with( nolock)
          WHERE  DBNAME = Db_name()
                 AND TABLENAME = @TABLE
                AND Index_Name NOT IN
                (SELECT constraintsname from DBA_Maintenance. dbo . source_table_constraints  with (nolock )
                                                WHERE  DBNAME = Db_name()
                                                AND TABLENAME = @TABLE
                )
              
          WHILE @I <= @COUNT
            BEGIN
                                     --print 1.1
                SELECT @tmp_index_name = index_name
                FROM   (SELECT Row_number()
                                 OVER(
                                   ORDER BY index_name) rn,
                               index_name
                        FROM   DBA_Maintenance .dbo . source_table_index WITH ( NOLOCK)
                        WHERE  DBNAME = Db_name()
                            AND TABLENAME = @TABLE
                            AND Index_Name NOT IN
                                            (SELECT constraintsname from DBA_Maintenance. dbo . source_table_constraints  with(nolock )
                                                        WHERE  DBNAME = Db_name()
                                                            AND TABLENAME = @TABLE
                                            )
                            )tmp
                WHERE  rn = @I
                SET @DEL_INDEX_SQL =N'DROP INDEX ' + @tmp_index_name + ' ON '
                                   + @TABLE
                    print 2
                    print @DEL_INDEX_SQL
                EXEC (@DEL_INDEX_SQL )
                SET @ERROR =@@ERROR + @ERROR
                SET @I =@I + 1
            END
                                   
    
                                     /*
    ----------重新创建 约束性索引
    ---由于EI字段设置的为 运行为 null。故不能创建带EI的约束，暂时屏蔽该部分
                    DECLARE @CREATE_CONSTRAINTS_SQL NVARCHAR(MAX)
                    DECLARE @CONSTRAINTS_INDEX_KEYS NVARCHAR(1024)
                    DECLARE @CONSTRAINTS_INDEX_NAME SYSNAME
                    DECLARE @XJ INT=1
                    DECLARE @XJCOUNT INT
    
                    SELECT @XJCOUNT=COUNT(*) FROM  [DBA_Maintenance].[dbo].[source_table_index]  WITH(NOLOCK)
                                WHERE DBNAME = Db_name()
                                    AND TABLENAME = @TABLE
                                    AND  Index_description like 'clustered,%'
                    WHILE @XJ<=@XJCOUNT
                    BEGIN
                        SELECT @CONSTRAINTS_INDEX_NAME=index_name,
                                        @CONSTRAINTS_INDEX_KEYS=index_keys
                        FROM (SELECT index_name ,index_keys,ROW_NUMBER() OVER( ORDER BY index_name)RN
                        FROM   [DBA_Maintenance].[dbo].[source_table_index] WITH(NOLOCK)
                                WHERE DBNAME = Db_name()
                                AND TABLENAME = @TABLE
                                AND  Index_description like 'clustered,%'
                                )AA
                        WHERE RN=@XJ
    
                        IF CHARINDEX(',EI',@CONSTRAINTS_INDEX_KEYS)>=1 or CHARINDEX('EI,',@CONSTRAINTS_INDEX_KEYS)>=1
                        BEGIN
                                        SET @CREATE_CONSTRAINTS_SQL='ALTER TABLE ' + @TABLE + '+ Char(9)  ADD CONSTRAINT '
                                            + 'EI_'+@CONSTRAINTS_INDEX_NAME
                                            + ' PRIMARY KEY CLUSTERED (' + @CONSTRAINTS_INDEX_KEYS
                                            + ')'
                        END
                        ELSE
                        BEGIN
                                        SET @CREATE_CONSTRAINTS_SQL='ALTER TABLE ' + @TABLE + '+ Char(9)  ADD CONSTRAINT '
                                            + 'EI_'+@CONSTRAINTS_INDEX_NAME
                                            + ' PRIMARY KEY CLUSTERED ( EI,' + @CONSTRAINTS_INDEX_KEYS
                                            + ')'
                        END
                        --print 2
                        --print @CREATE_CONSTRAINTS_SQL
                EXEC (@CREATE_CONSTRAINTS_SQL)
                SET @XJ=@XJ+1
    
        END
    */
        ---重新创建聚集索引 select * from [DBA_Maintenance].[dbo].[source_table_index]  where Index_description like 'clustered%' and Index_description not like 'clustered,%'
    
            DECLARE @X INT= 1
            DECLARE @XCOUNT INT= 0
            DECLARE @CREATE_CLUSTERED_INDEX_SQL NVARCHAR( MAX)
            DECLARE @CLUSTERED_INDEX_NAME SYSNAME
            DECLARE @CLUSTER_INDEX_KEYS NVARCHAR( 1024)
            IF @TABLE <>'FeedPermission'
            BEGIN
                SELECT @XCOUNT =COUNT (*)
                    FROM [DBA_Maintenance] .[dbo] . [source_table_index]
                        WHERE DBNAME = Db_name()
                            AND TABLENAME = @TABLE
                            AND Index_description like 'clustered%'
                          --AND Index_description not like 'clustered,%'  ---同 约束性索引的原因一样
                WHILE @X <=@XCOUNT
                BEGIN
                SELECT @CLUSTERED_INDEX_NAME =Index_name ,
                @CLUSTER_INDEX_KEYS=Index_keys
                FROM (SELECT index_name, index_keys , ROW_NUMBER() OVER( ORDER BY index_name )RN
                        FROM   [DBA_Maintenance] .[dbo] .[source_table_index] WITH(NOLOCK )
                            WHERE DBNAME = Db_name()
                                AND TABLENAME = @TABLE
                                AND  Index_description like 'clustered%'
                                --AND Index_description not like 'clustered,%' ---同 约束性索引的原因一样
                                )AA
                        WHERE RN =@X
    
                IF CHARINDEX (',EI' ,@CLUSTER_INDEX_KEYS)>= 1 or CHARINDEX('EI,' ,@CLUSTER_INDEX_KEYS)>= 1
                BEGIN
                        SET @CREATE_CLUSTERED_INDEX_SQL ='CREATE CLUSTERED INDEX EI_'+@CLUSTERED_INDEX_NAME + Char (9)+ 'ON '+ Char( 9) +@TABLE+ '(' + @CLUSTER_INDEX_KEYS+ ')'
                END
                ELSE
                BEGIN
                        SET @CREATE_CLUSTERED_INDEX_SQL ='CREATE CLUSTERED INDEX EI_'+@CLUSTERED_INDEX_NAME + Char (9)+ 'ON '+ Char( 9) +@TABLE+ '( EI,' + @CLUSTER_INDEX_KEYS+ ')'
                END
    
                PRINT 3
                print @CREATE_CLUSTERED_INDEX_SQL
                EXEC (@CREATE_CLUSTERED_INDEX_SQL )
                                   
                SET @X =@X + 1
            END
            END
            ELSE
            BEGIN
                CREATE CLUSTERED INDEX EI_PK_FeedPermission_EmployeeID_Feed ON FeedPermission(EI , EmployeeID , FeedID )
            END
    
    ---根据维护库中的备份索引属性信息 删除表上对应的非约束类索引(非聚集索引）
            DECLARE @Y INT= 1
            DECLARE @YCOUNT INT= 0
            DECLARE @NONCLUSTERED_INDEX_NAME SYSNAME
            DECLARE @NON_INDEX_KEYS NVARCHAR( 1024)
            DECLARE @CREATE_NON_INDEX_SQL NVARCHAR( MAX)
            DECLARE @INDEX_INCLUDE_KEYS NVARCHAR( 512)
    
            IF  @TABLE <> 'FeedPermission'
            BEGIN
                SELECT @YCOUNT = Count(*)
                FROM   DBA_Maintenance .dbo . source_table_index
                WHERE  DBNAME = Db_name()
                    AND TABLENAME = @TABLE
                    AND Index_description like 'nonclustered%'
    
                WHILE @Y <= @YCOUNT
                        BEGIN
                            SELECT @NONCLUSTERED_INDEX_NAME = index_name,
                                @NON_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 . source_table_index
                                                WHERE  DBNAME = Db_name()
                                                AND TABLENAME = @TABLE
                                                AND Index_description like 'nonclustered%' )tmp
                                    WHERE  rn = @Y
                --由于程序和存储过程中有一个存在指定强制使用feedwork表上的XI_ExecuterID,故这个需单独考虑
    
                            IF @NONCLUSTERED_INDEX_NAME ='XI_ExecuterID' and @TABLE ='FeedWork'
                            BEGIN
                                    SET @CREATE_NON_INDEX_SQL =N'CREATE NONCLUSTERED INDEX XI_ExecuterID ON FeedWork(EI, ExecuterID, Deadline, Status)'
                            END
                            ELSE
                            BEGIN
                            IF rtrim (ltrim ( @NON_INDEX_KEYS ))='EI'
                            BEGIN
                                IF @INDEX_INCLUDE_KEYS IS NOT NULL AND @INDEX_INCLUDE_KEYS<>''
                                BEGIN
                                SET @CREATE_NON_INDEX_SQL =N'CREATE NONCLUSTERED INDEX ' + Char (9 )+
                                    + 'EI_' +@NONCLUSTERED_INDEX_NAME + Char (9 ) + ' ON ' + Char (9)
                                    + @TABLE + '(' + @NON_INDEX_KEYS + ')
                                    INCLUDE(' + @INDEX_INCLUDE_KEYS + ')'
                                END
                                ELSE
                                BEGIN
                                SET @CREATE_NON_INDEX_SQL =N'CREATE NONCLUSTERED INDEX ' + Char (9 )+
                                    + 'EI_' +@NONCLUSTERED_INDEX_NAME + Char (9 ) + ' ON ' + Char (9)
                                    + @TABLE + '(' + @NON_INDEX_KEYS + ')'
                                END
                            END
                            ELSE
                            BEGIN
                                IF CHARINDEX (',EI' ,@NON_INDEX_KEYS)>= 1 or CHARINDEX('EI,' ,@NON_INDEX_KEYS )>= 1
                                BEGIN
                                    IF @INDEX_INCLUDE_KEYS IS NOT NULL AND @INDEX_INCLUDE_KEYS<> ''
                                    BEGIN
                                        SET @CREATE_NON_INDEX_SQL =N'CREATE NONCLUSTERED INDEX ' + Char (9 )+
                                                + 'EI_' +@NONCLUSTERED_INDEX_NAME + Char (9 ) + ' ON ' + Char ( 9)
                                                + @TABLE + '(' + @NON_INDEX_KEYS + ')
                                            INCLUDE(' + @INDEX_INCLUDE_KEYS + ')'
                                    END
                                    ELSE
                                    BEGIN
                                        SET @CREATE_NON_INDEX_SQL =N'CREATE NONCLUSTERED INDEX ' + Char (9 )+
                                                + 'EI_' +@NONCLUSTERED_INDEX_NAME + Char (9 ) + ' ON ' + Char ( 9)
                                                + @TABLE + '(' + @NON_INDEX_KEYS + ')'
                                    END
                                END
                                ELSE
                                BEGIN
                                    IF @INDEX_INCLUDE_KEYS IS NOT NULL AND @INDEX_INCLUDE_KEYS<> ''
                                    BEGIN
                                        SET @CREATE_NON_INDEX_SQL =N'CREATE NONCLUSTERED INDEX ' + Char (9 )+
                                        + 'EI_' +@NONCLUSTERED_INDEX_NAME + Char (9) + ' ON ' + Char( 9)
                                        + @TABLE + '(EI,' + @NON_INDEX_KEYS + ')
                                        INCLUDE(' + @INDEX_INCLUDE_KEYS + ')'
                                    END
                                    ELSE
                                    BEGIN
    
                                    SET @CREATE_NON_INDEX_SQL =N'CREATE NONCLUSTERED INDEX ' + Char (9 )+
                                                + 'EI_' +@NONCLUSTERED_INDEX_NAME + Char (9) + ' ON ' + Char( 9)
                                                + @TABLE + '(EI,' + @NON_INDEX_KEYS + ')'
                                    END
                                END
                            END
                        END
    
                        PRINT 4
                        print @CREATE_NON_INDEX_SQL
    
                        EXEC (@CREATE_NON_INDEX_SQL )
                        SET @ERROR =@@ERROR + @ERROR
                        SET @Y =@Y + 1
                    END
                END
                ELSE
                BEGIN
                    CREATE NONCLUSTERED INDEX EI_PK_FeedPermission_Feed_EmployeeID ON FeedPermission(EI , FeedID , EmployeeID )
                    CREATE NONCLUSTERED INDEX EI_IX_FeedPermission_EmployeeID_FeedID_InfoType ON FeedPermission(EI ,EmployeeID , FeedID , InfoType)
                END
    
          IF @ERROR = 0
            BEGIN
                COMMIT TRANSACTION TRA_NAME
            END
          IF @ERROR <> 0
            BEGIN
                ROLLBACK TRANSACTION TRA_NAME
            END
                                     DROP TABLE #ALL_INDEX
    
          SET NOCOUNT OFF
    
      END