Rebuild all the Indexes of a SQL Database in one go

Introduction

In my last post, I had explained what could be the best value of Fill Factor for the indexes in SQL Server and had promised to show a handy way to ReBuild all the indexes including the ones created on the Indexed Views.

Implementation

USE DBName

GO

DECLARE @tsql NVARCHAR(MAX)  

DECLARE @fillfactor INT

SET @fillfactor = 70 

SELECT @tsql = 

  STUFF(( SELECT DISTINCT 

           ';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

          FROM 

           sysobjects o 

          INNER JOIN sysindexes i 

           ON o.id = i.id 

          WHERE 

           o.xtype IN ('U','V')

           AND i.name IS NOT NULL

          FOR XML PATH('')), 1,1,'')

--PRINT @tsql          

EXEC sp_executesql @tsql  

Conclusion

This way, we can easily rebuild all the existing indexes on the Tables as well as the Indexed Views of the selected Database with an option to set the fillfactor as well. Hope, this script will prove to be handy.