MSSQL Index Fragmentation Rebuild

mssql-index-defrag.sql

--this will generate only ready-to-run sql statements to rebuild. 


select
	'ALTER INDEX [' + a.Name + '] ON ' + a.Schema_Name + '.[' + a.[Table_Name] + '] ' +
	(CASE when b.AverageFragmentation > 30 then 'REBUILD WITH (ONLINE = ON)' else 'REORGANIZE' end) + ';'
	cmd, b.AverageFragmentation,b.[page_count] 
FROM 
(               
	SELECT s.name [Schema_Name], tbl.name AS [Table_Name], tbl.object_id, i.name AS [Name], i.index_id, CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered], 
	CAST(case when i.type=3 then 1 else 0 end AS bit) AS [IsXmlIndex], CAST(case when i.type=4 then 1 else 0 end AS bit) AS [IsSpatialIndex]
    FROM
    sys.tables AS tbl
	inner join sys.schemas s ON s.schema_id = tbl.schema_id
    INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
) a
inner join
(               
	SELECT 
		tbl.object_id, i.index_id, 
		fi.avg_fragmentation_in_percent AS [AverageFragmentation], 
		fi.page_count as [page_count]
    FROM
    sys.tables AS tbl
    INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
    INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
) b on a.object_id=b.object_id and a.index_id=b.index_id
where b.AverageFragmentation > 5 and b.[page_count] > 500
order by b.AverageFragmentation desc;