Reindex Online tables - maintenance_re_index

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
maintenance_re_index
*/
ALTER Procedure maintenance_re_index
(
    @MaxFragmentation int = 10 ,
    @TrivialPageCount int = 100 ,
    @RebuildThreshold int = 40
)
as
BEGIN

SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @HasBlobColumn int;

--DECLARE @MaxFragmentation int;
--DECLARE @TrivialPageCount int;
--DECLARE @RebuildThreshold int;

---- Tuning constants
--SET @MaxFragmentation = 10 --Change this value to adjust the threshold for fragmentation
--SET @RebuildThreshold = 30 --Change this value to adjust the break point for defrag/rebuild
--SET @TrivialPageCount = 1000 --Change this value to adjust the size threshold

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
if(OBJECT_ID('tempdb..#work_to_do')>1)
    DROP TABLE #work_to_do;

SELECT distinct
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @MaxFragmentation
AND index_id > 0 -- cannot defrag a heap
AND page_count > @TrivialPageCount -- ignore trivial sized indexes

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT distinct WTD.*
FROM #work_to_do WTD
INNER JOIN sys.indexes I ON I.object_id = WTD.objectid
WHERE I.is_disabled = 0 AND I.is_hypothetical = 0;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)
    BEGIN;
        FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
            SET @HasBlobColumn = 0 -- reinitialize
            SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
            FROM sys.objects AS o
            JOIN sys.schemas AS s ON s.schema_id = o.schema_id  WHERE o.object_id = @objectid;
           
            SELECT @indexname = QUOTENAME(name)
            FROM sys.indexes
            WHERE object_id = @objectid AND index_id = @indexid;

            SELECT @partitioncount = count (*)
            FROM sys.partitions
            WHERE object_id = @objectid AND index_id = @indexid;

            -- Check for BLOB columns
            IF @indexid = 1 -- only check here for clustered indexes ANY blob column on the table counts
                SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
                FROM sys.objects SO
                inner join sys.columns SC ON SO.Object_id = SC.object_id
                inner join sys.types ST ON SC.system_type_id = ST.system_type_id AND
                (
                    ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
                    OR
                    ST.name IN ('text', 'ntext', 'image', 'xml')
                )
                WHERE SO.Object_ID = @objectID
            ELSE -- nonclustered. Only need to check if indexed column is a BLOB
                SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
                FROM sys.objects SO
                INNER JOIN sys.index_columns SIC ON SO.Object_ID = SIC.object_id
                INNER JOIN sys.Indexes SI ON SO.Object_ID = SI.Object_ID AND SIC.index_id = SI.index_id
                INNER JOIN sys.columns SC ON SO.Object_id = SC.object_id AND SIC.Column_id = SC.column_id
                INNER JOIN sys.types ST ON SC.system_type_id = ST.system_type_id AND
                (
                    ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
                    OR
                    ST.name IN ('text', 'ntext', 'image', 'xml')
                )
                WHERE SO.Object_ID = @objectID
           
                SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname;
                IF @frag > @RebuildThreshold
                BEGIN
                    SET @command = @command + N' REBUILD'
                    IF @HasBlobColumn = 1
                        SET @command = @command + N' WITH( SORT_IN_TEMPDB = ON) '
                    ELSE
                        SET @command = @command + N' WITH( ONLINE = ON, SORT_IN_TEMPDB = ON) '
                END
                ELSE
                    SET @command = @command + N' REORGANIZE'
                    IF @partitioncount > 1
                        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
                        PRINT N'Executing: ' + @command +' Has Blob = ' + convert(nvarchar(2),@HasBlobColumn);
                       
                        --PRINT  @command
                        --print 'print '''+@command +''''
                        --print 'Go'
                        EXEC (@command)
        END;
        -- Close and deallocate the cursor.
        CLOSE partitions;
        DEALLOCATE partitions;
END