Create Index Rebuild Script With DMV

Creating an operation script via T-SQL is a common way. With T-SQL code following, you can create index rebuild script for all indexes which is fragmented more then 20 % .


Be aware that some indexes may not be rebuilt online. For this issue just remove "online=on," part from the script.

select
' alter index ['+i.name+'] on ['+OBJECT_SCHEMA_NAME(f.object_id)+'].['+object_name(f.object_id)+'] rebuild with(online=on, maxdop=8)
GO
'
from sys.dm_db_index_physical_stats(db_id(),null,null,null,'sampled')  f
inner join sys.indexes i on f.object_id=i.object_id and f.index_id= i.index_id
where avg_fragmentation_in_percent > 20
order by avg_fragmentation_in_percent desc