SQL Server Rebuild, Recreated, Reindex Table Indexes

12/10/2013 5:48:18 PM

Rebuild, recreate, or reindex indexes on SQL Server tables that have fragmentation.

DECLARE @TableName varchar(200),
		@IndexName varchar(200),
		@SqlCommand nvarchar(500)
		
DECLARE TablesAndIndexes CURSOR FOR
SELECT	Tables.Name as TableName,
		Indexes.Name as IndexName		
FROM	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS Stats
		inner join sys.indexes as Indexes on Stats.Index_id = Indexes.Index_ID and Stats.object_ID = Indexes.object_ID
		inner join sys.objects as Tables on Indexes.Object_ID = Tables.Object_ID
WHERE	Indexes.Name IS NOT NULL
		AND avg_fragmentation_in_percent > 10.0
		AND Stats.Index_id > 0
		AND Page_Count > 300

OPEN	TablesAndIndexes
FETCH	TablesAndIndexes
INTO	@TableName,
		@IndexName

-- start the main processing loop.
WHILE @@Fetch_Status = 0   
	BEGIN
		--Print
		Print('Rebuilding: ' + @IndexName + ' ON ' + @TableName)
		--Rebuild
		SET @SqlCommand = 'ALTER INDEX ' + @IndexName + ' ON [' + @TableName + '] REBUILD'
		EXEC (@SqlCommand)
			
		-- Get the next row.		
		FETCH	TablesAndIndexes
		INTO	@TableName,
				@IndexName
	END
CLOSE TablesAndIndexes
DEALLOCATE TablesAndIndexes