SET NOCOUNT ON
DECLARE @objectid int
DECLARE @indexid int
DECLARE @partitioncount bigint
DECLARE @schemaname nvarchar(130)
DECLARE @objectname nvarchar(130)
DECLARE @indexname nvarchar(130)
DECLARE @partitionnum bigint
DECLARE @partitions bigint
DECLARE @frag float 
DECLARE @command nvarchar(4000)
-- 有条件地选择碎片率超10%的sys.dm_db_index_physical_stats函数表和索引，并将其转换对象和索引ID名。
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

-- 声明游标要处理的分区的列表。
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do

-- 打开游标.
OPEN partitions

-- 遍历分区。
WHILE (1=1)
    BEGIN
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag
        IF @@FETCH_STATUS < 0 BREAK
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(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 = QUOTENAME(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
         
          SET @command = N‘ALTER INDEX ‘ + @indexname + N‘ ON ‘ + @schemaname + N‘.‘ + @objectname + N‘ REBUILD PARTITION = ALL‘
--
        EXEC (@command)
        PRINT N‘Executed: ‘ + @command
    END

-- 关闭游标.
CLOSE partitions
DEALLOCATE partitions

-- 删除临时表.
DROP TABLE #work_to_do
GO

本文出自 “IT” 博客，请务必保留此出处http://8577754.blog.51cto.com/8567754/1553174

重建索引