Uncategorized

Generate indexes REBUILD/REORGANIZE script based on fragmentation level

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT 
    CONCAT('/* ', [n].[SchemaName], '.', [n].[TableName], ' (', [d].[record_count], ') [', [d].[page_count] , '] */ ') IndexInfo,
    CONCAT ('ALTER INDEX ', QUOTENAME([i].[name]), ' ON ', QUOTENAME([n].[SchemaName]), '.', QUOTENAME([n].[TableName]),
			 CASE 
                WHEN [d].[avg_fragmentation_in_percent] > 30 THEN ' REBUILD WITH (ONLINE=ON);'
                ELSE ' REORGANIZE;'
            END),           
    ISNULL( [i].[name], '{HEAP}' ) [IndexName],
    [d].[avg_fragmentation_in_percent] [AvgFragmentationPercent], 
    [d].[page_count] [PageCount],     
    [d].[record_count] [RowCount]
FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, 'SAMPLED') [d]
	INNER JOIN [sys].[objects] [o] ON [o].[object_id] = [d].[object_id]
	INNER JOIN [sys].[indexes] [i] ON [i].[object_id] = [o].[object_id] AND [i].[index_id] = [d].[index_id]
	OUTER APPLY	( SELECT OBJECT_SCHEMA_NAME([d].[object_id], [d].[database_id]) [SchemaName],
						 OBJECT_NAME([d].[object_id], [d].[database_id]) [TableName] ) [n]
WHERE 
	[d].[database_id] = DB_ID() AND 
	[d].[page_count] > 64 AND 
	[d].[avg_fragmentation_in_percent] > 5
ORDER BY 
	OBJECT_SCHEMA_NAME([d].[object_id], [d].[database_id]),
	OBJECT_NAME([d].[object_id], [d].[database_id])