How to Rebuild All SQL Server Indexes Online

How to Rebuild All SQL Server Indexes Online - Article on TechHowTos.com

The below script makes use of the undocumented SQL Server stored procedure “sp_MSforeachtable” and with the proper syntax, it rebuilds all SQL Server indexes online for all tables in a database, along with keeping the default Fill Factor for each index.

Rebuild all indexes online along with keeping up the the default fill factor:

USE [DATABASE_NAME];
GO
EXEC sp_MSforeachtable @command1="print 'rebuilding indexes in table: ?'", @command2="ALTER INDEX ALL ON ?
REBUILD WITH (ONLINE=ON)";
GO

Note: The above script assumes that your current edition of SQL Server supports online index rebuild. In a different case you can modify the script to perform the index rebuild offline.

 

Rebuild All SQL Server Indexes Online With a Single T-SQL Statement - Article on TechHowTos.com

 

By modifying the above script, you can have different variations of the rebuild statement (i.e. run the rebuild offline, set the Fill Factor, etc.).

Here are some more examples/variations of the above script.

Rebuild all indexes online along with setting up the fill factor value:

USE [SampleDB1];
GO
EXEC sp_MSforeachtable @command1="print 'rebuilding indexes in table: ?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON, FILLFACTOR=[FILL_FACTOR_PERCENTAGE])";
GO

 

Rebuild all indexes offline along with keeping up the default fill factor value:

USE [SampleDB1];
GO
EXEC sp_MSforeachtable @command1="print 'rebuilding indexes in table: ?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)";
GO

 

Rebuild all indexes offline along with setting up the fill factor value:

USE [SampleDB1];
GO
EXEC sp_MSforeachtable @command1="print 'rebuilding indexes in table: ?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF, FILLFACTOR=[FILL_FACTOR_PERCENTAGE])";
GO

I have written an article on SQLNetHub, on which you can find more info and examples. Check out the article here.

Read some of our other Database-related articles:

 

Also, if you are looking for the ultimate index rebuild script, make sure you check out my article on MSSQLTips!

Reference: TechHowTos.com (http://www.techhowtos.com)

© TechHowTos.com

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 4.78 out of 5)

Loading...

Share this!
About Artemakis Artemiou 28 Articles
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.