SET NOCOUNT ON;
DECLARE @tablename VARCHAR(255), @indexname VARCHAR(255), @execstr VARCHAR(255);
DECLARE @tableid int, @indexid int, @schema_id int;
DECLARE @frag decimal, @MaxFrag decimal, @MinPages int, @fillfactor int, @MinRebuilFrag decimal;
SET @MaxFrag       = 20.0;-- Decide on the maximum fragmentation to allow for.
SET @MinPages      = 100  ;-- Decide on the Minimum pages for index to be rebuilt instead of defraged
Set @MinRebuilFrag = 70.0;-- Decide on the maximum fragmentation allowed before rebuilding after a defrag
EXEC sp_updatestats;

   -- Defrag if they have more pages than min pages value, and less fragmentation than rebuild level
   DECLARE indexes CURSOR FOR
     select t.name as tname, i.name as iname, i_stats.avg_fragmentation_in_percent as frag, t.Object_id as tableid, i.object_id as indexid
       from sys.tables t
          inner join sys.indexes i on i.object_id = t.object_id
          inner join sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) i_stats on i.object_id = i_stats.object_id
       where i.index_id > 0
          and i.type in (1, 2) -- clustered & nonclustered only
          and i.is_unique_constraint = 0 -- do not include UQ
          and i.is_disabled = 0 and i.is_hypothetical = 0
          and i_stats.avg_fragmentation_in_percent < @MaxFrag
          and i_stats.page_count > @MinPages
        order by i_stats.avg_fragmentation_in_percent + i_stats.page_count desc
    -- Open the cursor. Loop through the indexes.
   OPEN indexes; FETCH NEXT FROM indexes INTO @tablename, @indexname, @frag, @tableid, @indexid; WHILE @@FETCH_STATUS = 0
      BEGIN;
         SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(CONVERT(VARCHAR(15),@tableid)) + ', ''' + @indexname + ''')';
         PRINT convert(varchar,getdate(),121) + ': Executing '+@execstr+' - fragmentation currently '+RTRIM(CONVERT(VARCHAR(15),@frag))+'% in '+@tablename+' '+@indexname;
         EXEC (@execstr);
         FETCH NEXT FROM indexes INTO @tablename, @indexname, @frag, @tableid, @indexid;
       END;CLOSE indexes; DEALLOCATE indexes;
   
   -- Rebuild small indexes
   DECLARE indexes CURSOR FOR
     select top 200 t.name as tname, i.name as iname, i_stats.avg_fragmentation_in_percent as frag, t.Object_id as tableid, i.object_id as indexid, schema_id
       from sys.tables t
          inner join sys.indexes i on i.object_id = t.object_id
          inner join sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) i_stats on i.object_id = i_stats.object_id
       where i.index_id > 0
          and i.type in (1, 2) -- clustered & nonclustered only
          and i.is_unique_constraint = 0 -- do not include UQ
          and i.is_disabled = 0 and i.is_hypothetical = 0
          and i_stats.avg_fragmentation_in_percent > @MaxFrag
          and i_stats.page_count < (@MinPages + 1)
        order by i_stats.avg_fragmentation_in_percent desc
    -- Open the cursor. Loop through the indexes.
   OPEN indexes; FETCH NEXT FROM indexes INTO @tablename, @indexname, @frag, @tableid, @indexid, @schema_id; WHILE @@FETCH_STATUS = 0
      BEGIN;
         select @execstr = 'ALTER INDEX ' +  @indexname + ' ON '+ SCHEMA_NAME(@schema_id) + '.' + @tablename + ' REBUILD'; 
         PRINT convert(varchar,getdate(),121) + ': Executing '+@execstr+' - fragmentation currently '+RTRIM(CONVERT(VARCHAR(15),@frag))+'% in '+@tablename+' '+@indexname;
         EXEC (@execstr);
         FETCH NEXT FROM indexes INTO @tablename, @indexname, @frag, @tableid, @indexid, @schema_id;
       END;CLOSE indexes; DEALLOCATE indexes;

   -- rebuild indexes greater than rebuild level
   DECLARE indexes CURSOR FOR
     select top 200 t.name as tname, i.name as iname, i_stats.avg_fragmentation_in_percent as frag, t.Object_id as tableid, i.object_id as indexid, schema_id
       from sys.tables t
          inner join sys.indexes i on i.object_id = t.object_id
          inner join sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) i_stats on i.object_id = i_stats.object_id
       where i.index_id > 0
          and i.type in (1, 2) -- clustered & nonclustered only
          and i.is_unique_constraint = 0 -- do not include UQ
          and i.is_disabled = 0 and i.is_hypothetical = 0
          and i_stats.avg_fragmentation_in_percent >= @MinRebuilFrag
          and i_stats.page_count > @MinPages
        order by i_stats.avg_fragmentation_in_percent desc
    -- Open the cursor. Loop through the indexes.
   OPEN indexes; FETCH NEXT FROM indexes INTO @tablename, @indexname, @frag, @tableid, @indexid, @schema_id; WHILE @@FETCH_STATUS = 0
      BEGIN;
         select @execstr = 'ALTER INDEX ' + @indexname + ' ON ' + SCHEMA_NAME(@schema_id) + '.' + @tablename + ' REBUILD'; 
         PRINT convert(varchar,getdate(),121) + ': Executing '+@execstr+' - fragmentation currently '+RTRIM(CONVERT(VARCHAR(15),@frag))+'% in '+@tablename+' '+@indexname;
         EXEC (@execstr);
         FETCH NEXT FROM indexes INTO @tablename, @indexname, @frag, @tableid, @indexid, @schema_id;
       END;CLOSE indexes; DEALLOCATE indexes;

EXEC sp_updatestats;