Sqlserver2005及以上索引重置代码

频繁写入操作，导致索引冗余混乱。特保存一下索引重置代码

use DB_XX
go

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) 

SELECT [object_id] AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag 
INTO #IndexInfo 
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 #IndexInfo
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

	-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. 
    IF @frag < 30.0 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'
    IF @frag >= 30.0 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'
    IF @partitioncount > 1 
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))

    EXEC (@command) 
END

CLOSE partitions; 
DEALLOCATE partitions; 

DROP TABLE #IndexInfo; 

GO