SQL Stored Procedure to Rebuild or Reorganise Fragmented Database Indexes

A re-useable Stored Procedure that you can call to re-index any database by entering the name of the database.

This is a handy little Stored Procedure that you can literally copy and paste into your Master database, to re-index all of the tables within a particular database.

The most useful part of this is that you enter the database name when you call the procedure, meaning you can loop through all of your databases in one go by calling the same procedure as follows, if you have linked servers you can also call it from another server when called correctly. 

EXEC DatabaseReIndex 'YourDatabaseName'
EXEC DatabaseReIndex 'YourDatabaseName2'

While it is possible to run this during working hours, it is always more sensible to run this when there is very little work being done on the server.

SQL

USE master;

GO

CREATE PROC DatabaseReIndex(@Database VARCHAR(100)) AS BEGIN

DECLARE @DbID SMALLINT=DB_ID(@Database)--Get Database ID

IF  EXISTS(SELECT * FROM tempdb.sys.objects WHERE name='Indexes') BEGIN--Delete Temp Table if exists, then create

DROP TABLE TempDb.dbo.Indexes

END

CREATE TABLE TempDb.dbo.Indexes(IndexTempID INT IDENTITY(1,1),SchemaName NVARCHAR(128),TableName NVARCHAR(128),IndexName NVARCHAR(128),IndexFrag FLOAT)

EXEC ('USE '+@Database+';

INSERT INTO TempDb.dbo.Indexes(TableName,SchemaName,IndexName,IndexFrag)

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,sch.name,ind.name IndexName,indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats('+@DbID+', NULL, NULL, NULL, NULL) indexstats

INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id

INNER JOIN sys.objects obj on obj.object_id=indexstats.object_id

INNER JOIN sys.schemas as sch ON sch.schema_id = obj.schema_id

WHERE indexstats.avg_fragmentation_in_percent > 10 AND indexstats.index_type_desc<>''HEAP''

ORDER BY indexstats.avg_fragmentation_in_percent DESC')--Get index data and fragmentation, set the percentage as high or low as you need

DECLARE @IndexTempID BIGINT=0,@SchemaName NVARCHAR(128),@TableName NVARCHAR(128),@IndexName NVARCHAR(128),@IndexFrag FLOAT

SELECT * FROM TempDb.dbo.Indexes--View your results, comment out if not needed...

-- Loop through the indexes

WHILE @IndexTempID IS NOT NULL BEGIN

    SELECT @SchemaName=SchemaName,@TableName=TableName,@IndexName=IndexName,@IndexFrag=IndexFrag FROM TempDb.dbo.Indexes WHERE IndexTempID=@IndexTempID

    IF @IndexName IS NOT NULL AND @SchemaName IS NOT NULL AND @TableName IS NOT NULL BEGIN

    IF @IndexFrag<30. BEGIN--Low fragmentation can use re-organise, set at 30 as per most articles

    PRINT 'USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REORGANIZE'

    EXEC('USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REORGANIZE')

    END

    ELSE BEGIN--High fragmentation needs re-build

    PRINT 'USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REBUILD'

    EXEC('USE '+@Database+'; ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @TableName + N' REBUILD')

    END

    END

    SET @IndexTempID=(SELECT MIN(IndexTempID) FROM TempDb.dbo.Indexes WHERE IndexTempID>@IndexTempID)

END

END

DROP TABLE TempDb.dbo.Indexes

GO

Ousia Logo