Rebuild & Defragment Indexes

Index Rebuild and Defrag

--Get the DB ID
DECLARE @lpDataBaseID int;
SET @lpDataBaseID = DB_ID(N'MYDBNAME');

--REBUILD ANY INDEXES > 25% FRAGMENTED

	DECLARE @opRebuildCommand VARCHAR(MAX) 
	DECLARE opCursor CURSOR LOCAL FAST_FORWARD FOR      

		SELECT
			'ALTER INDEX ALL ON MYDBNAME.' + tblSchemas.name + '.' + tblObjects.name + ' REBUILD'
		FROM 
			sys.dm_db_index_physical_stats(@lpDataBaseID,null,null,null,null) AS tblStats
			INNER JOIN
			sys.objects AS tblObjects
				ON tblStats.object_id = tblObjects.object_id
			INNER JOIN 
			sys.schemas AS tblSchemas
				ON tblObjects.schema_id = tblSchemas.schema_id			
		WHERE
			avg_fragmentation_in_percent >= 25		
		ORDER BY
			avg_fragmentation_in_percent
			DESC

	OPEN opCursor 
	FETCH NEXT FROM opCursor INTO @opRebuildCommand 
	WHILE @@fetch_status = 0     
		BEGIN     
			EXEC(@opRebuildCommand)
			FETCH NEXT FROM opCursor INTO @opRebuildCommand     
		END 
	CLOSE opCursor 
	DEALLOCATE opCursor	


--CREATE A CURSOR TO EXECUTE A DEFRAG(REORGANISE) ON ALL INDEXES BETWEEN 5% AND 30% FRAGMENTATION

	DECLARE @opDefragCommand VARCHAR(MAX) 
	DECLARE opCursor CURSOR LOCAL FAST_FORWARD FOR      

		SELECT
			'ALTER INDEX ALL ON MYDBNAME.' + tblSchemas.name + '.' + tblObjects.name + ' REORGANIZE'
		FROM 
			sys.dm_db_index_physical_stats(@lpDataBaseID,null,null,null,null) AS tblStats
			INNER JOIN
			sys.objects AS tblObjects
				ON tblStats.object_id = tblObjects.object_id
			INNER JOIN 
			sys.schemas AS tblSchemas
				ON tblObjects.schema_id = tblSchemas.schema_id			
		WHERE
			avg_fragmentation_in_percent < 30		
			AND
			avg_fragmentation_in_percent >= 5
		ORDER BY
			avg_fragmentation_in_percent
			DESC

	OPEN opCursor 
	FETCH NEXT FROM opCursor INTO @opDefragCommand 
	WHILE @@fetch_status = 0     
		BEGIN     
			EXEC(@opDefragCommand)
			FETCH NEXT FROM opCursor INTO @opDefragCommand     
		END 
	CLOSE opCursor 
	DEALLOCATE opCursor

GO