SET NOCOUNT ON;
DECLARE @MaxFrag varchar(10), @MinPages varchar(10), @MinRebuilFrag varchar(10);
SET @MaxFrag       = '25'   ;-- Decide on the maximum fragmentation to allow for.
SET @MinPages      = '1000' ;-- Decide on the Minimum pages for index to be rebuilt instead of defraged
Set @MinRebuilFrag = '80'   ;-- Decide on the maximum fragmentation allowed before rebuilding after a defrag

DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(4000)
DECLARE database_cursor CURSOR FOR
SELECT name
  FROM MASTER.sys.sysdatabases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'Resource','distribution' , 'reportserver', 'reportservertempdb')
  OPEN database_cursor; FETCH NEXT FROM database_cursor INTO @DB_Name; WHILE @@FETCH_STATUS = 0
   BEGIN
    SELECT @Command = 'use ' + @DB_Name + ' EXEC sp_updatestats;'
    EXEC sp_executesql @Command

    -- Defrag if they have more pages than min pages value, and less fragmentation than rebuild level
    SELECT @Command = 'use ' + @DB_Name + '; SET QUOTED_IDENTIFIER ON; 
      DECLARE @tablename VARCHAR(255), @indexname VARCHAR(255), @execstr VARCHAR(255);
      DECLARE @tableid int, @indexid int, @schema_id int;

      DECLARE indexes CURSOR FOR
      select t.name as tname, i.name as iname, 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 indexes; FETCH NEXT FROM indexes INTO @tablename, @indexname, @tableid, @indexid; WHILE @@FETCH_STATUS = 0
       BEGIN;
         SELECT @execstr = ''DBCC INDEXDEFRAG (0, '' + RTRIM(CONVERT(VARCHAR(15),@tableid)) + '', '''''' + @indexname + '''''')'';
         EXEC (@execstr);
         FETCH NEXT FROM indexes INTO @tablename, @indexname, @tableid, @indexid;
       END;CLOSE indexes; DEALLOCATE indexes;
    '
   EXEC sp_executesql @Command

   -- Rebuild small indexes
   SELECT @Command = 'use ' + @DB_Name + '; SET QUOTED_IDENTIFIER ON; 
     DECLARE @tablename VARCHAR(255), @indexname VARCHAR(255), @execstr VARCHAR(255);
     DECLARE @tableid int, @indexid int, @schema_id int;
     DECLARE indexes CURSOR FOR
     select top 200 t.name as tname, i.name as iname, 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 * i_stats.page_count desc
     OPEN indexes; FETCH NEXT FROM indexes INTO @tablename, @indexname, @tableid, @indexid, @schema_id; WHILE @@FETCH_STATUS = 0
      BEGIN;
         select @execstr = ''ALTER INDEX '' +  @indexname + '' ON ''+ SCHEMA_NAME(@schema_id) + ''.'' + @tablename + '' REBUILD'';
         EXEC (@execstr);
         FETCH NEXT FROM indexes INTO @tablename, @indexname, @tableid, @indexid, @schema_id;
       END;CLOSE indexes; DEALLOCATE indexes;
    '
   EXEC sp_executesql @Command

   -- rebuild indexes greater than rebuild level
   SELECT @Command = 'use ' + @DB_Name + '; SET QUOTED_IDENTIFIER ON; 
     DECLARE @tablename VARCHAR(255), @indexname VARCHAR(255), @execstr VARCHAR(255);
     DECLARE @tableid int, @indexid int, @schema_id int;

     DECLARE indexes CURSOR FOR
     select top 100 t.name as tname, i.name as iname, 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 + i_stats.page_count desc
     OPEN indexes; FETCH NEXT FROM indexes INTO @tablename, @indexname, @tableid, @indexid, @schema_id; WHILE @@FETCH_STATUS = 0
      BEGIN;
         select @execstr = ''ALTER INDEX '' +  @indexname + '' ON ''+ SCHEMA_NAME(@schema_id) + ''.'' + @tablename + '' REBUILD'';
         EXEC (@execstr);
         FETCH NEXT FROM indexes INTO @tablename, @indexname, @tableid, @indexid, @schema_id;
       END;CLOSE indexes; DEALLOCATE indexes;
    '
   EXEC sp_executesql @Command

   SELECT @Command = 'use ' + @DB_Name + ' EXEC sp_updatestats;'
   EXEC sp_executesql @Command
   FETCH NEXT FROM database_cursor INTO @DB_Name

END;CLOSE database_cursor;DEALLOCATE database_cursor