批量修改索引名称为IX_[UQ_]表名_列名1_列名2...

BEGIN

    --批量修改索引名称为IX_[UQ_]表名_列名1_列名2...

    DECLARE @TableName sysname

    DECLARE @IndexName sysname

    DECLARE @Flag sysname

    SELECT TableName=O.name, IndexName=IDX.name, ColumnName=C.name,

        Flag=CASE WHEN IDX.is_unique=0 THEN 'IX_' ELSE 'IX_UQ_' END

    INTO #Index

    FROM sys.indexes IDX

    JOIN sys.index_columns IDXC

      ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id

    JOIN sys.objects O

      ON O.[object_id]=IDX.[object_id]

    JOIN sys.columns C

      ON O.[object_id]=C.[object_id]

     AND O.type='U'

     AND O.is_ms_shipped=0

     AND IDXC.column_id=C.column_id

    LEFT JOIN sysobjects D

           ON O.object_id=D.parent_obj AND D.xtype='PK' AND D.name=IDX.name

    WHERE IsNull(D.xtype, '')<>'PK' AND IDX.is_disabled<>1 AND is_included_column=0

    ORDER BY O.name, IDX.name

    DECLARE mycur CURSOR LOCAL

        FOR

        SELECT TableName, IndexName, Flag FROM #Index GROUP BY TableName, IndexName, Flag

    OPEN mycur

    FETCH NEXT FROM mycur

    INTO @TableName, @IndexName, @Flag

    WHILE @@fetch_Status=0

    BEGIN

        DECLARE @OldIndex Varchar(200) =''

        DECLARE @NewIndex Varchar(200) =''

        DECLARE @ColumnName Varchar(200) =''

        SELECT @ColumnName=@ColumnName

                           +CASE WHEN @ColumnName='' THEN ColumnName ELSE '_'+ColumnName END

        FROM #Index

        WHERE TableName=@TableName AND IndexName=@IndexName

        SET @OldIndex=@TableName+'.'+@IndexName

        SET @NewIndex=@Flag+@TableName+'_'+@ColumnName

        SET @OldIndex=IsNull(@OldIndex, '')

        SET @NewIndex=IsNull(@NewIndex, '')

        SET @ColumnName=IsNull(@ColumnName, '')

        IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name=@NewIndex)

       AND (@OldIndex<>'' AND @NewIndex<>'' AND @ColumnName<>'')

       AND Len(@NewIndex)<128

        BEGIN

            EXEC sp_rename @OldIndex, @NewIndex, 'INDEX'

            PRINT @OldIndex+'→'+@NewIndex

        END

        ELSE IF EXISTS (SELECT * FROM sys.indexes WHERE name=@NewIndex)AND @IndexName<>@NewIndex

        BEGIN

            EXEC(' DROP INDEX '+@IndexName+' ON '+@TableName)

            PRINT(' DROP INDEX '+@IndexName+' ON '+@TableName)

        END

        ELSE

            PRINT('Nothing TO DO !')

        FETCH NEXT FROM mycur

        INTO @TableName, @IndexName, @Flag --逐条读取   

    END

    CLOSE mycur

    DEALLOCATE mycur

    DROP TABLE #Index

    END