[MS SQL] 將資料表索引進行重建(REBUILD & REORGANIZE)

  將資料表索引進行重建(REBUILD & REORGANIZE) 

DECLARE @dbName NVARCHAR(128)
SET @dbName = DB_NAME()

DECLARE @schemaName NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @indexName NVARCHAR(128)
DECLARE @indexId INT
DECLARE @fragPercent FLOAT
DECLARE @sql NVARCHAR(MAX)

-- 迴圈檢查每個索引的碎片程度
DECLARE indexCursor CURSOR FOR
SELECT
	s.name AS schemaName,
	t.name AS tableName,
	i.name AS indexName,
	i.index_id AS indexId,
	ips.avg_fragmentation_in_percent AS fragPercent
FROM 
	sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
	INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
	INNER JOIN sys.tables t ON i.object_id = t.object_id
	INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE 
	ips.avg_fragmentation_in_percent > 10 -- 篩選需要維護的索引
	AND i.type IN (1, 2)	-- 只處理 Clustered 和 Non-Clustered 索引
	AND i.is_disabled = 0	-- 忽略已停用的索引
ORDER BY tableName

OPEN indexCursor
FETCH NEXT FROM indexCursor INTO @schemaName, @tableName, @indexName, @indexId, @fragPercent

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @fragPercent > 30
		SET @sql = N'ALTER INDEX [' + @indexName + N'] ON [' + @dbName + N'].[' + @schemaName + N'].[' + @tableName + N'] REBUILD;'
	ELSE
		SET @sql = N'ALTER INDEX [' + @indexName + N'] ON [' + @dbName + N'].[' + @schemaName + N'].[' + @tableName + N'] REORGANIZE;'

	PRINT '執行: ' + @sql
	EXEC sp_executesql @sql

	FETCH NEXT FROM indexCursor INTO @schemaName, @tableName, @indexName, @indexId, @fragPercent
END

CLOSE indexCursor
DEALLOCATE indexCursor

參考網址: 

By Hao★