|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
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 |