Rebuild all database indexes Rebuild all database indexes

When I am performing local development, on occasion I need a lot of data populated in attempt to test certain features with - what I consider - an insane amount of data in my MSSQL database. During this process I often create the data then proceed to delete the data. When perform mass inserts and deletes, it is easy for your database indexes to become fragmented. Here is a handy tool that will rebuild all database indexes on your database. In case you need a refresher, I've previously written about the importance of database indexing.

Disclaimer: I don't suggest using this in production, this is for local development purposes only as it uses some cursors to perform the looping based on the retrieval of a dynamic list of indexes on my database.

Let's look at a complete example now:

DECLARE @Database VARCHAR(255)
DECLARE @fillfactor INT = 90
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
OPEN DatabaseCursor  
FETCH NEXT FROM DatabaseCursor INTO @Database
   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''   
   -- create table cursor
   EXEC (@cmd)
   OPEN TableCursor   
   FETCH NEXT FROM TableCursor INTO @Table
           SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
           EXEC (@cmd) 
       FETCH NEXT FROM TableCursor INTO @Table
   CLOSE TableCursor
   DEALLOCATE TableCursor  
   FETCH NEXT FROM DatabaseCursor INTO @Database
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

The process starts by getting all databases on the server excluding some system tables. This is stored into a cursor and begins looping through the databases. This is quite similar to using loops with the JavaScript Fetch API.

Next, it does a similar process and creates a cursor with all tables from the current database being looped.

And finally the command to rebuild the indexes is run: ALTER INDEX ALL ON. There you have it, all indexes on all databases will now be rebuilt so you are back into a fresh index state after performing mass manipulation of your data.

Published on Feb 21, 2020

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | indexes

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.


Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.