Rebuild all database indexes

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