Wednesday, 30 January 2019

SQL Server Reindexing Query (By Index)

This query is used to reindex the SQL Server database tables index by index

DECLARE @tableName NVARCHAR(500) 
DECLARE @indexName NVARCHAR(500) 
DECLARE @indexType NVARCHAR(55) 
DECLARE @sql NVARCHAR(500) 
DECLARE @percentFragment DECIMAL(11, 2) 
DECLARE fragmentedtablelist CURSOR FOR 
  SELECT '[' + Object_schema_name(ind.object_id) 
         + '].[' + Object_name(ind.object_id) + ']' AS TableName, 
         '[' + ind.NAME + ']'                       AS IndexName, 
         indexstats.index_type_desc                 AS IndexType, 
         indexstats.avg_fragmentation_in_percent 
  FROM   sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, NULL) 
         indexstats 
         INNER JOIN sys.indexes ind 
                 ON ind.object_id = indexstats.object_id 
                    AND ind.index_id = indexstats.index_id 
  WHERE 
    --indexstats.avg_fragmentation_in_percent > 5 AND 
    ind.NAME IS NOT NULL 
  ORDER  BY tablename, 
            ind.NAME 

OPEN fragmentedtablelist 

FETCH next FROM fragmentedtablelist INTO @tableName, @indexName, @indexType, @percentFragment 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @sql = 'ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REBUILD; ' 

      PRINT CONVERT(VARCHAR, Getdate(), 14) + ' - ' +
            @indexName + ' - ' +
            Cast(@percentFragment AS NVARCHAR(50)) + ' - ' +
            ( @sql ) 

      EXEC (@sql) 

      FETCH next FROM fragmentedtablelist INTO @tableName, @indexName, @indexType, @percentFragment 
  END 

CLOSE fragmentedtablelist 
DEALLOCATE fragmentedtablelist