Script to Rebuild/Reorganize indexes in SQL Server
Below script is usefull to pull out the Index details from a respective database and create script for rebuilding or reorganizing the indexes depending upon the FRAGMENTATION PERCENTAGE.
If fragmentation percentage is less than 30% then REORGANIZE INDEX script will be considered and for more than that percentage, REBUILD OF INDEX will be considered.
Below query works perfectly in 2005/2008 and higher versions of SQL Server.
There will an option included in the output script and that is ONLINE=ON. This will get executed only in 2005 Enterprise edition and 2008 Enterprise, Developer and Evaluation Edition.
If suppose you are running it in the versions not mentioned above, then please remove the option (ONLINE=ON) and execute it.
--------------------------------------------
SELECT
db_name(a.database_id) [Db Name]
,object_name(a.object_id) Table_Name
,a.index_id
,b.name
,a.avg_fragmentation_in_percent
,record_count,a.avg_fragment_size_in_pages,page_count,fragment_count,b.fill_factor,a.avg_page_space_used_in_percent,a.record_count,
case when a.avg_fragmentation_in_percent > 30 then 'ALTER INDEX '+b.name +' on '+db_name(a.database_id)+'.'+OBJECT_SCHEMA_NAME((a.object_id))+'.'+object_name(a.object_id)+' REBUILD with (FILLFACTOR= 80 , SORT_IN_TEMPDB = ON, ONLINE = ON) '
when a.avg_fragmentation_in_percent < 30 then 'ALTER INDEX '+b.name +' on '+db_name(a.database_id)+'.'+OBJECT_SCHEMA_NAME((a.object_id))+'.'+object_name(a.object_id)+' REORGANIZE ' END as [SCRIPT]
FROM sys.dm_db_index_physical_stats (DB_ID(), null,NULL, NULL, 'DETAILED') AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id
where a.database_id = db_id()
--and (OBJECT_NAME(b.object_id) IN ('')) --Give table names if specific condition is there
and a.avg_fragmentation_in_percent <>0
ORDER BY a.object_id
----------------------------------------
Thank You.
