Created
April 12, 2015 05:44
-
-
Save palpalani/6cf7f50f9855b835591c to your computer and use it in GitHub Desktop.
Rebuild Indexes of all tables in a single MSSQL database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment