Sunday 5 May 2013

SQL script to rebuild all indexes for a table

Ideally I would recommend to use Ola Hallengren's SQL Maintenance Solution but in some cases you may have to rebuild indexes on individual tables manually. Following script will allow you to do that. Just replace <TableName> with the actual table name and if you want to build the index offline (might cause locks) set @Build_Online to 0.

 DECLARE @IndexName VARCHAR (100);  
 DECLARE @TableName VARCHAR (100);  
 DECLARE @IndexId int;  
 DECLARE @Build_Online int;  
   
 SET @TableName = '<TableName>';  
 SET @Build_Online = 1;  
   
 DECLARE indexes_cursor CURSOR FOR SELECT  
      si.index_id,  
      si.name  
 FROM sys.objects so  
 INNER JOIN sys.indexes si  
      ON so.object_id = si.object_id  
 WHERE so.name = @TableName;  
   
 OPEN indexes_cursor;  
   
 FETCH NEXT FROM indexes_cursor  
 INTO @IndexId, @IndexName;  
   
 WHILE @@FETCH_STATUS = 0  
 BEGIN  
   
      PRINT 'Defragmenting ' + @TableName + ' --> ' + @IndexName  
   
      IF @Build_Online = 1  
      BEGIN  
           EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD WITH (ONLINE = ON)')  
      END  
      ELSE  
      BEGIN  
           EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD')  
      END  
        
      SELECT  
           *  
      FROM sys.dm_db_index_physical_stats  
      (  
      DB_ID('AxaptaLive'),  
      OBJECT_ID(@TableName),  
      @IndexId,  
      NULL,  
      NULL  
      );  
   
      FETCH NEXT FROM indexes_cursor  
      INTO @IndexId, @IndexName;  
   
 END  
   
 CLOSE indexes_cursor;  
 DEALLOCATE indexes_cursor;  
 GO  

This posting is provided "AS IS" with no warranties. Use code at your own risk.

No comments:

Post a Comment