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

前段时间由于业务结构重构，需要将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