Cacher is the code snippet organizer for pro developers

-- Reorg and Rebuild all Indexes - SQL Server 2005 and above 
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