Thursday, 14 February 2019

SQL Server Reindexing Query (by Table)

The below query to reindex the SQL Server database index by table

DECLARE @TableName VARCHAR(255) 
DECLARE @sql NVARCHAR(500) 
DECLARE @fillfactor INT 

SET @fillfactor = 80 

DECLARE tablecursor CURSOR FOR 
  SELECT '[' + Object_schema_name([object_id]) + '].[' 
         + NAME + ']' AS TableName 
  FROM   sys.tables 
  ORDER  BY NAME 

OPEN tablecursor 

FETCH next FROM tablecursor INTO @TableName 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @sql = 'ALTER INDEX ALL ON ' + @TableName 
                 + ' REBUILD WITH (FILLFACTOR = ' 
                 + CONVERT(VARCHAR(3), @fillfactor) + ')'
      PRINT ( @sql ) 
      EXEC (@sql) 
      FETCH next FROM tablecursor INTO @TableName 
  END 

CLOSE tablecursor 
DEALLOCATE tablecursor 

go