Script – Rebuild all indexes

Below is a script you can use to rebuild all indexes in a SQL Server database. This comes in super-handy for Azure SQL Databases and SQL Server Express Editions, as we don’t support Maintenance Plans with both.

SET NOCOUNT ON
GO

DECLARE rebuildindexes CURSOR
FOR
SELECT SysSche.Name, SysObj.Name
FROM         Sys.Objects SysObj
INNER JOIN         sys.schemas SysSche ON SysObj.Schema_ID = SysSche.Schema_ID
WHERE         TYPE = 'U'              
	AND object_id NOT IN (
		SELECT object_id
		FROM sys.external_tables
		)

OPEN rebuildindexes  

DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)  

FETCH NEXT
FROM rebuildindexes
INTO @tableSchema, @tableName  

WHILE (@@FETCH_STATUS = 0)
BEGIN
	  

	SET @Statement = 'ALTER INDEX ALL ON '   + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' REBUILD'   

	PRINT @Statement -- comment this print statement to prevent it from printing whenever you are ready to execute the command below.
	--EXEC sp_executesql @Statement -- remove the comment on the beginning of this line to run the commands 
	
	FETCH NEXT
	FROM rebuildindexes
	INTO @tableSchema, @tableName
END  

CLOSE rebuildindexes

DEALLOCATE rebuildindexes
GO

SET NOCOUNT OFF
GO

Alternatively, if you wish to rebuild only fragmented indexes then consider implementing MSSQL Tiger Team’s AdaptiveIndexDefrag solution to perform an intelligent defrag on one or more indexes and statistics for one or more databases. This solution is not yet supported with Azure SQL Database.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

SQL Shots with Dapinder Arora | SQL Server Solutions