Follow @endyourif rss Facebook LinkedIn

Rebuild all database indexes Rebuild all database indexes

Published on Feb 21, 2020

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 @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


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.

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.

Tags: SQL | indexes

My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP