Rebuild / Reorganize Based on Fragmentation Level

This Script Will  Rebuild / Reorganize Based on Fragmentation Level

 

 

--Rebuild and Reorganize Script
--Start of variables
use master
DECLARE @dbName sysname
DECLARE @tableName sysname
DECLARE @databaseID INT
DECLARE @objectID INT
DECLARE @indexID INT
DECLARE @fragPercent INT
DECLARE @cmd VARCHAR(5000)
DECLARE @objectName sysname
DECLARE @indexName sysname
-- End of Variables

-- The databaseCursor cursor selects the user databases

DECLARE databaseCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb','AdventureWorks2008','AdventureWorksDW','AdventureWorksDW2008','AdventureWorksLT','AdventureWorksLT2008','SQL2008SBS', 'SQL2008SBSFS')

OPEN databaseCursor

FETCH NEXT FROM databaseCursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

/* Selecting the required columns in the statsInfoTable table
for storing the selected values for the database. The table will be dropped
and recreated for each database*/


SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
INTO statsInfoTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10 and index_id > 0

-- Declaring cursor for selecting each value from the statsInfoTable

DECLARE physicalStatsCursor CURSOR FOR
SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
FROM statsInfoTable

OPEN physicalStatsCursor

FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent

WHILE @@FETCH_STATUS = 0

BEGIN

-- Select the Table name and Index name. The selected values will be
-- used for building the dynamic query for the Reorganize or Rebuild.
-- Reorganize is the online operation and Rebuild is the offline operation

SELECT @objectName = s.name + '.' + t.name FROM sys.objects t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.object_id = @objectID

SELECT @indexName = i.name
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.object_id = @objectID AND i.index_id = @indexID

IF (@fragPercent > 10 AND @fragPercent < 30)

BEGIN

PRINT 'Rorganizing Index' + @indexName + 'ON' + @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REORGANIZE'
PRINT @cmd
EXECUTE (@cmd)

END

IF (@fragPercent = 30 OR @fragPercent > 30)

BEGIN

PRINT 'Rebuilding Index' + @indexName + ' ' + 'ON' + ' ' + @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REBUILD'
PRINT @cmd
EXECUTE (@cmd)

END

FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent

END

CLOSE physicalStatsCursor

DEALLOCATE physicalStatsCursor

FETCH NEXT FROM databaseCursor INTO @dbName

DROP TABLE statsInfoTable

END

CLOSE databaseCursor

DEALLOCATE databaseCursor

--select * from statsInfoTable