sqlserver重建索引

`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;`