Monday 20 June 2011

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.

No comments:

Post a Comment