GO
DBCC DROPCLEANBUFFERS;
GO
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
