SQLServer重建所有索引

--EXEC [reBuildIndex]
--网上搜索到的代码，做了少许改动，增加日志输出
ALTER PROC [dbo].[reBuildIndex]
AS
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command VARCHAR(8000);
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;
SELECT object_id AS objectid,
       index_id AS indexid,
       partition_number AS partitionnum,
       avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
      AND index_id > 0;

SELECT *
FROM dbo.work_to_do;

DECLARE @count INT;
SELECT @count = COUNT(1)
FROM work_to_do;
PRINT '共计需要处理的索引数量为：' + CAST(@count AS VARCHAR);

DECLARE @begin DATETIME;
SET @begin = GETDATE();

DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
OPEN partitions;
FETCH NEXT FROM partitions
INTO @objectid,
     @indexid,
     @partitionnum,
     @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
    SELECT @objectname = o.name,
           @schemaname = s.name
    FROM sys.objects AS o
        JOIN sys.schemas AS s
            ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;
    SELECT @indexname = name
    FROM sys.indexes
    WHERE object_id = @objectid
          AND index_id = @indexid;
    SELECT @partitioncount = COUNT(*)
    FROM sys.partitions
    WHERE object_id = @objectid
          AND index_id = @indexid;
    PRINT '正在处理索引 ' + @objectname + ' -> ' + @indexname + ' 碎片率 ' + CAST(@frag AS VARCHAR) + '%';
    IF @frag < 30.0
    BEGIN;
        SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
        IF @partitioncount > 1
            SELECT @command = @command + ' PARTITION=' + CONVERT(CHAR, @partitionnum);
        PRINT '正在重新组织...';
        EXEC (@command);
    END;
    IF @frag >= 30.0
    BEGIN;
        SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
        IF @partitioncount > 1
            SELECT @command = @command + ' PARTITION=' + CONVERT(CHAR, @partitionnum);
        PRINT '正在重新生成...';
        EXEC (@command);
    END;
    FETCH NEXT FROM partitions
    INTO @objectid,
         @indexid,
         @partitionnum,
         @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

PRINT '处理完毕，共计耗时：' + CAST(DATEDIFF(SECOND, @begin, GETDATE()) AS VARCHAR) + '秒';

IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
    DROP TABLE work_to_do;