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:

[code]
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’msdb’,’tempdb’,’model’,’distribution’)
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN  

   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
   WHILE @@FETCH_STATUS = 0
   BEGIN   

           SET @cmd = ‘ALTER INDEX ALL ON ‘ + @Table + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
           EXEC (@cmd) 

       FETCH NEXT FROM TableCursor INTO @Table
   END   

   CLOSE TableCursor
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
[/code]

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.