SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Rebuild all the Indexes of a SQL Database in one go


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.


DECLARE @fillfactor INT
SET @fillfactor = 70 
SELECT @tsql = 
           ';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           sysobjects o 
          INNER JOIN sysindexes i 
           ON o.id = i.id 
           o.xtype IN ('U','V')
           AND i.name IS NOT NULL
          FOR XML PATH('')), 1,1,'')
--PRINT @tsql          
EXEC sp_executesql @tsql  


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.


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.