Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement