How to Rebuild All Indexes Online for a SQL Server Database

This article, discusses about, how you can rebuild all indexes online for a SQL Server database and provides useful T-SQL scripts that can help you perform this task.

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.

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.

Also, you can check my article on MSSQLTips.com via which, I provide a comprehensive script to Manage SQL Server Rebuilds and Reorganize for Index Fragmentation.

---

Read Also:

• Benefits of Primary Keys in Database Tables
• Understanding Artificial Intelligence: A Human-Centric Overview
• Addressing AI Risks: Achieving the AI Risk Management Professional Certification
• Mastering Scaled Scrum: Earning the Scaled Scrum Professional Certification
• Strengthening Agile Leadership: Achieving the Scrum Master Professional Certificate
• Advancing My Expertise in AI: Earning the CAIEC Certification
• Achieving the CAIPC Certification: Advancing My AI Expertise

 

Subscribe to the GnoelixiAI Hub newsletter on LinkedIn and stay up to date with the latest AI news and trends.

Subscribe to my YouTube channel.

 

Reference: aartemiou.com (https://www.aartemiou.com)
© Artemakis Artemiou

Rate this article: (No Ratings Yet)

Loading...