Problem: Many a times you need to defragment indexes on SQL server. Following query is quite handy for doing this task.
Solution:
SELECT 'Alter index ' + i.NAME + ' on ' + Quotename(s.NAME) + '.' + Quotename(o.NAME) + CASE WHEN stats.avg_fragmentation_in_percent >=60 THEN ' rebuild ' ELSE ' reorganize' END FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, 'LIMITED') AS stats, sys.objects AS o, sys.schemas AS s, sys.indexes AS i WHERE o.object_id = stats.object_id AND s.schema_id = o.schema_id AND i.object_id = stats.object_id AND i.index_id = stats.index_id AND stats.avg_fragmentation_in_percent >= 20 AND stats.page_count >= 1000 ORDER BY stats.avg_fragmentation_in_percent DESC, stats.page_count DESC
