SQL Server - Rebuild Indexes

rebuildIndexes.sql

BEGIN
    set quoted_identifier on
    
    DECLARE @db varchar(max) = 'MyDB'
    DECLARE @today VARCHAR(9) = datename(w,sysdatetime())
    
    DECLARE @tableName varchar(max)
    DECLARE @indexName varchar(max)
    DECLARE @sql Nvarchar(max)
    
    declare c cursor for
    SELECT t.name as table_name, i.name as index_name
      FROM sys.dm_db_index_physical_stats (DB_ID(@db), NULL, NULL, NULL, NULL ) as s
      join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
      join sys.tables t on t.object_id = s.object_id
     where avg_fragmentation_in_percent > 30
       and page_count > 1000
       and i.name is not null
       and ( t.name not like '%xyz%' ) /* exclude tables for weekly rebuilds */
     order by 1, 2
    
    open c
    fetch next from c into @tableName, @indexName
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
      BEGIN TRY
          set @sql = 'alter index [' + @indexName + '] on ' + @tableName + ' rebuild with (sort_in_tempdb=on, online=on, maxdop = 1)'
          EXECUTE sp_executesql @sql
      END TRY
      BEGIN CATCH
          /* somebody used a depricated lob */
          set @sql = 'alter index [' + @indexName + '] on ' + @tableName + ' reorganize'
          EXECUTE sp_executesql @sql
      END CATCH
      print @sql
      FETCH NEXT FROM c INTO @tableName, @indexName
    END
    
    CLOSE c
    DEALLOCATE c
    
    IF @today = 'Sunday'
    BEGIN
        /* weekly rebuilds */
    END
    
END
exec sp_updatestats