Rebuild Indexes of all tables in a single MSSQL database

rebuild-indexes.sql

/*
MSSQL
-----
Rebuilds indexes on smallest tables first, allowing the maximum number of indexes to be rebuilt in the shortest amount of time.
Real time progress updates, allowing you to estimate how much time is remaining before completion.
Correctly handles multiple schemas, a common flaw in other scripts.
*/
SET NOCOUNT ON
GO
 
--Set the fillfactor
DECLARE @FillFactor TINYINT
SELECT @FillFactor=80
 
DECLARE @StartTime DATETIME
SELECT @StartTime=GETDATE()
 
if object_id('tempdb..#TablesToRebuildIndex') is not null
begin
drop table #TablesToRebuildIndex
end
 
DECLARE @NumTables VARCHAR(20)
 
SELECT
s.[Name] AS SchemaName,
t.[name] AS TableName,
SUM(p.rows) AS RowsInTable
INTO #TablesToRebuildIndex
FROM
sys.schemas s
LEFT JOIN sys.tables t
ON  s.schema_id = t.schema_id
LEFT JOIN sys.partitions p
ON  t.object_id = p.object_id
LEFT JOIN sys.allocation_units a
ON  p.partition_id = a.container_id
WHERE
p.index_id IN ( 0, 1 ) -- 0 heap table , 1 table with clustered index
AND p.rows IS NOT NULL
AND a.type = 1  -- row-data only , not LOB
GROUP BY
s.[Name],
t.[name]
SELECT @NumTables=@@ROWCOUNT
 
DECLARE RebuildIndex CURSOR FOR
SELECT
ROW_NUMBER() OVER (ORDER BY ttus.RowsInTable),
ttus.SchemaName,
ttus.TableName,
ttus.RowsInTable
FROM
#TablesToRebuildIndex AS ttus
ORDER BY
ttus.RowsInTable
OPEN RebuildIndex
 
DECLARE @TableNumber VARCHAR(20)
DECLARE @SchemaName NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @RowsInTable VARCHAR(20)
DECLARE @Statement NVARCHAR(300)
DECLARE @Status NVARCHAR(300)
 
FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @Status='Table '+@TableNumber+' of '+@NumTables+': Rebuilding indexes on '+@SchemaName+'.'+@tablename + ' ('+@RowsInTable+' rows)'
RAISERROR (@Status, 0, 1) WITH NOWAIT  --RAISERROR used to immediately output status
 
SET @Statement = 'ALTER INDEX ALL ON ['+@SchemaName+'].['+@tablename +'] REBUILD WITH (FILLFACTOR = '+CONVERT(VARCHAR(3), @FillFactor)+' )'
EXEC sp_executesql @Statement
 
FETCH NEXT FROM RebuildIndex INTO @TableNumber, @SchemaName, @tablename, @RowsInTable
END
 
CLOSE RebuildIndex
DEALLOCATE RebuildIndex
 
drop table #TablesToRebuildIndex
 
Print 'Total Elapsed Time: '+CONVERT(VARCHAR(100), DATEDIFF(minute, @StartTime, GETDATE()))+' minutes'
 
GO