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])