Programmatically rebuilding all index of a database or for all databases (TSQL Code/Script)


Generally we use Maintenance Plan to rebuild all indexes of all tables of a database. However if you want to accomplish similar task programmatically, below script/s (TSQL) will be beneficial.

Please replace the value of @fillfactor and Database Name (highlighted in yellow) with the required values from your script.  

Script to rebuild all indexes of a database:

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT
SET @fillfactor = 90  -- Change this fillfactor as per your requirement
DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases  
WHERE name = '[Database Name]'
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'''  
   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) 
       print 'All index rebuilt...on table:'
       print @Table
       FETCH NEXT FROM TableCursor INTO @Table  
   END  
   CLOSE TableCursor  
   DEALLOCATE TableCursor 
   FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor


Script to rebuild all indexes of all database:

DECLARE @Database VARCHAR(255)  
DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT
SET @fillfactor = 90  -- Change this fillfactor as per your requirement
DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases  
WHERE name NOT IN  '[tempdb]' – Here we are excluding tempdb which means all other database will be reindexed.
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'''  
   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) 
       print 'All index rebuilt...on table:'
       print @Table
       FETCH NEXT FROM TableCursor INTO @Table  
   END  
   CLOSE TableCursor  
   DEALLOCATE TableCursor 
   FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor 

Comments

Follower said…
really helpful posting

Popular Posts