Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Comments

Posted by Jason Brimhall on 21 June 2011

Nice script.  I would be careful running this against large databases in production.  

Posted by jclare on 27 June 2011

I get these errors. I tried on 2005 and 2008. I'm new but this looks like a great script so I'd like to know what I am doing wrong.

Msg 1088, Level 16, State 9, Line 1

Cannot find the object "Address" because it does not exist or you do not have permissions.

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@tsql".

Posted by vinay pugalia on 27 June 2011

I have tested the scripts and they work well on both the versions you have mentioned. Anyways, lets try to solve out your issue -

1. For the error - Must declare the scalar variable "@tsql"

Just make sure that you are not executing the script by selecting any part of it. Run it as-is.

2. For the error - Cannot find the object "Address" because it does not exist or you do not have permissions. --- Make sure that the Object "Address" exists on your DB and the user executing the script has appropriate permissions.

Please revert with your findings !

Leave a Comment

Please register or log in to leave a comment.