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.

About the author

By Jamie

My Books