exec sp_updatestats;
select TableName = OBJECT_NAME(ind.OBJECT_ID) ,IndexName = ind.name ,IndexType = indexstats.index_type_desc ,Fragmentation = indexstats.avg_fragmentation_in_percent ,SqlQuery = 'ALTER INDEX ' + QUOTENAME(ind.name) + ' ON ' +QUOTENAME(object_name(ind.object_id)) + CASE WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD ' WHEN indexstats.avg_fragmentation_in_percent>=5 THEN ' REORGANIZE ' ELSE NULL END -- if <5 not required, so no query needed from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) indexstats inner join sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id where 1=1 and ind.Name is not null and indexstats.avg_fragmentation_in_percent >= 5 order by indexstats.avg_fragmentation_in_percent DESC
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.