SSDB.Check_Index_Fragmentation.sql

/*--------------------------------------------------------------------------------------+
| Purpose:	How to Check Index Fragmentation on Indexes in a Database
+---------------------------------------------------------------------------------------*/

SELECT 
	  [Schema] = dbschemas.[name]
	, [Table] = dbtables.[name]
	, [Index] = dbindexes.[name]
	, indexstats.[avg_fragmentation_in_percent]
	, indexstats.[page_count]
	, [SqlScript] = 
		CASE 
			WHEN indexstats.[avg_fragmentation_in_percent] > 30 THEN 'ALTER INDEX [' + dbindexes.[name] + '] ON [' + dbschemas.[name] + '].[' + dbtables.[name] + '] REBUILD  WITH (ONLINE = ON)'
			WHEN indexstats.[avg_fragmentation_in_percent] > 5 AND indexstats.[avg_fragmentation_in_percent] < 30 THEN 'ALTER INDEX [' + dbindexes.[name] + '] ON [' + dbschemas.[name] + '].[' + dbtables.[name] + '] REORGANIZE'
			ELSE NULL
		END 	
FROM 
	[sys].[dm_db_index_physical_stats] (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
	INNER JOIN [sys].[tables] AS dbtables ON dbtables.[object_id] = indexstats.[object_id]
	INNER JOIN [sys].[schemas] AS dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
	INNER JOIN [sys].[indexes] AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id
WHERE 
	1=1
	AND indexstats.[database_id] = DB_ID()
	AND dbindexes.[name] IS NOT NULL
	--AND dbindexes.[name] = 'IX_IndexName'
ORDER BY 
	indexstats.[avg_fragmentation_in_percent] DESC