The intent of this SQL Server query is to:Dynamically work rebuild/reorganize all indexes in all tables in all databases on a server. Rebuild or reorganize depending on the specified fragmentation threshold values.  Attempt to perform an online rebuild.  If an online rebuild can not be performed due to column types, do an offline rebuild if allowed, otherwise just do a reorganize.Excludes system databases like master, msdb, tempdb, model, and distribution I'm interested in a code review for correctness in logic.--Reorganize and/or rebuild in all tables on all databases based on fragmentation thresholdsDECLARE @iReorganizeMinThreshold INT = NULL; --Fragmentation amount between this and the rebuild threshold will cause a reorganize (NULL = never)DECLARE @iRebuildMinThreshold INT = NULL; --Fragmentation amount above this value will cause a rebuild (-1 = always rebuild, NULL = never)DECLARE @bAllowOfflineRebuild BIT = 0; --If true, indexes will be rebuilt even if they can't be rebuilt with online option.  If false they will be reogranized instead--If youre running SQL Server 2012 SP1  SP2, parallel online index rebuilds can cause corruption.--https://sqlperformance.com/2014/06/sql-indexes/hotfix-sql-2012-rebuilds--https://support.microsoft.com/en-ca/help/2969896/fix-data-corruption-occurs-in-clustered-index-when-you-run-online-index-rebuild-in-sql-server-2012-or-sql-server-2014PRINT 'Rebuilding/Reorganizing all indexes on every table in every database...'DECLARE @tblIndexes AS TABLE(    DatabaseName VARCHAR(255),    SchemaName VARCHAR(255),    TableName VARCHAR(255),    IndexName VARCHAR(255),    AvgPageFragmentation INT,    CanRebuildOnline BIT)PRINT 'Gethering indexes from all databases..'INSERT INTO @tblIndexes  EXEC sp_MSforeachdb'USE [?]SELECT         [Database] = DB_NAME(dps.DATABASE_ID),         [Schema] = OBJECT_SCHEMA_NAME(dps.OBJECT_ID),        [Table] = OBJECT_NAME(dps.OBJECT_ID),          [Index] = si.NAME,        [AvgPageFragmentation] = dps.AVG_FRAGMENTATION_IN_PERCENT,        [CanRebuildOnline] =        CASE            WHEN si.type_desc = ''CLUSTERED'' THEN                CASE WHEN NOT EXISTS                 (                    SELECT                         *                    FROM                         sys.[tables] AS tbl                    JOIN                         sys.[all_columns] col ON tbl.[object_id] = col.[object_id]                      WHERE                         tbl.[object_id] = si.[object_id] AND                         (                            (col.system_type_id IN (34,35,99,241)) OR                             (col.system_type_id IN (167,231,165) AND max_length = -1)                        )                 ) THEN 1 ELSE 0 END            WHEN si.type_desc <> ''CLUSTERED'' THEN                 CASE WHEN NOT EXISTS                (                    SELECT                         *                     FROM                         sys.[tables] AS tbl                    JOIN                         sys.[indexes] idx ON tbl.[object_id] = idx.[object_id]                    JOIN                         sys.[index_columns] ic ON idx.[object_id] = ic.[object_id] AND                                                     idx.[index_id] = ic.[index_id]                    JOIN                         sys.[all_columns] col ON tbl.[object_id] = col.[object_id] AND                                                 ic.[column_id] = col.[column_id]                    WHERE                         tbl.[object_id] = si.[object_id] AND                         si.[object_id] = idx.[object_id] AND                         si.index_id = idx.index_id AND                         (                            (col.system_type_id IN (34,35,99,241)) OR                             (col.system_type_id IN (167,231,165) AND max_length = -1)                        )                 ) THEN 1 ELSE 0 END            ELSE NULL        ENDFROM     sys.indexes siJOIN     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) dps ON dps.object_id = si.object_id AND dps.index_id = si.index_idJOIN    sys.tables t ON t.object_id = dps.object_idWHERE    t.type = ''U'' AND    dps.alloc_unit_type_desc = ''IN_ROW_DATA'' AND    dps.index_type_desc <> ''HEAP'' AND      DB_NAME(dps.DATABASE_ID) NOT IN (''master'',''msdb'',''tempdb'',''model'',''distribution'')ORDER BY    [Database],    [Schema],    [Table];'SELECT * FROM @tblIndexes;DECLARE @sDatabase VARCHAR(255);DECLARE @sSchema VARCHAR(255);DECLARE @sTable VARCHAR(255);DECLARE @sIndex VARCHAR(255);DECLARE @iFragmentation INT;DECLARE @sAlter varchar(200);DECLARE @bCanRebuildOnline BIT;DECLARE curIndexes CURSOR FORSELECT         [DatabaseName],         [SchemaName],        [TableName],          [IndexName],        [AvgPageFragmentation],        [CanRebuildOnline]FROM    @tblIndexes;--Iterate through each index, rebuild or reorganize or leave alone based on the amount of fragmentationOPEN curIndexesFETCH NEXT FROM curIndexes INTO    @sDatabase,    @sSchema,    @sTable,    @sIndex,    @iFragmentation,    @bCanRebuildOnline;WHILE @@FETCH_STATUS = 0BEGIN    IF @iReorganizeMinThreshold IS NOT NULL AND @iFragmentation BETWEEN @iReorganizeMinThreshold and @iRebuildMinThreshold     BEGIN               PRINT CONVERT(VARCHAR,GETDATE(),121) + ' - Reorganizing index [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';        SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable + '] REORGANIZE;';    END    ELSE IF @iRebuildMinThreshold IS NOT NULL AND @iFragmentation > @iRebuildMinThreshold     BEGIN         --An online rebuild operation cannot be performed on some indexes lif the index contains columns of data type text, ntext, image or FILESTREAM. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.        IF @bCanRebuildOnline = 1        BEGIN            PRINT CONVERT(VARCHAR,GETDATE(),121) + ' - Rebuilding index (online) [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';            SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' +  @sTable + '] REBUILD WITH (ONLINE=ON, MAXDOP=1);';         END        ELSE        BEGIN            IF @bAllowOfflineRebuild = 1            BEGIN                PRINT CONVERT(VARCHAR,GETDATE(),121) + ' - Rebuilding index (offline) [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';                SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' +  @sTable + '] REBUILD;';             END            ELSE            BEGIN                PRINT CONVERT(VARCHAR,GETDATE(),121) + ' - Reorganizing index (online rebuild not possible, offline not allowed) [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%...';                SET @sAlter = 'ALTER INDEX [' + @sIndex + '] ON [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable + '] REORGANIZE;';            END        END    END    ELSE     BEGIN        PRINT CONVERT(VARCHAR,GETDATE(),121) + ' - Skipping INDEX  [' + @sIndex + '] ON Table [' + @sDatabase + '].[' + @sSchema + '].[' + @sTable +'], fragmentation is at ' + CAST(@iFragmentation AS VARCHAR(15)) + '%.';    END    --Force refresh of print output    --RAISERROR (N'', -- Message text --          10, -- Severity --          1) -- State  --     WITH NOWAIT    EXEC(@sAlter);    FETCH NEXT FROM curIndexes INTO        @sDatabase,        @sSchema,        @sTable,        @sIndex,        @iFragmentation,        @bCanRebuildOnline;END;CLOSE curIndexes;DEALLOCATE curIndexes;
Dynamically re-index all indexes in all databases on server
sql;sql server