sp_IndexRebuild (Reorganize/Rebuild)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Sp_IndexRebuild') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.Sp_IndexRebuild
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Sp_IndexRebuild
AS
BEGIN
    DECLARE @vSQL                               VARCHAR(MAX)
    DECLARE @vTable                             VARCHAR(255)
    DECLARE @vSchemaName                        VARCHAR(255)
    DECLARE @vIndexName                         VARCHAR(255)
    DECLARE @vAvgFragmentationInPercent         DECIMAL(8,2)
    DECLARE @vFillFactor                        INT
    DECLARE @vReorganizeThreshold               VARCHAR(10)
    DECLARE @vRebuildThreshold                  VARCHAR(10)
    DECLARE @vMessage                           VARCHAR(MAX)
    DECLARE @vReorganizeRebuildFlag             INT
    DECLARE @vLogID                             INT

    SET NOCOUNT ON

    SET @vFillFactor = 100
    SET @vReorganizeThreshold = '10.0' -- Percent
    SET @vRebuildThreshold = '30.0' -- Percent

    BEGIN TRY

        -- ensure the temporary table does not exist
        IF (SELECT OBJECT_ID('tempdb..#FragmentedTableList')) IS NOT NULL
        BEGIN
            DROP TABLE #FragmentedTableList
        END
       
        SELECT
            OBJECT_NAME(ips.[object_id])    AS TableName
           ,avg_fragmentation_in_percent    AS avg_fragmentation_in_percent   
           ,si.name                         AS IndexName
           ,schema_name(st.[schema_id])     AS SchemaName
           ,0                               AS IsProcessed
        INTO
            #FragmentedTableList
        FROM
            sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) AS ips
            INNER JOIN
            sys.tables AS st
            ON
            ips.[object_id] = st.[object_id]
            INNER JOIN
            sys.indexes AS si
            ON
            ips.[object_id] = si.[object_id]
            AND
            ips.index_id = si.index_id
            INNER JOIN
            sys.partitions AS sp
            ON
            si.[object_id] = sp.[object_id]
            AND
            si.index_id = sp.index_id
        WHERE
            st.is_ms_shipped = 0
            AND
            si.name IS NOT NULL
            AND
            ips.avg_fragmentation_in_percent >= CAST(@vReorganizeThreshold AS DECIMAL(8,2))
            AND
            sp.[rows] > 100000
        ORDER BY
            SCHEMA_NAME(st.[schema_id])
           ,OBJECT_NAME(ips.[object_id])
           ,si.name


        WHILE EXISTS (SELECT 1 FROM #FragmentedTableList WHERE IsProcessed = 0 )
        BEGIN

            SELECT TOP 1
                @vSchemaName                = SchemaName
               ,@vTable                     = TableName
               ,@vIndexName                 = IndexName
               ,@vAvgFragmentationInPercent = avg_fragmentation_in_percent
            FROM
                #FragmentedTableList
            WHERE
                IsProcessed = 0

           
            SELECT @vReorganizeRebuildFlag =
                CASE
                    WHEN @vAvgFragmentationInPercent >= @vReorganizeThreshold
                    AND
                    @vAvgFragmentationInPercent < @vRebuildThreshold              THEN 1
                    WHEN @vAvgFragmentationInPercent >= @vRebuildThreshold        THEN 2
                END
           
            -- Log Index Rebuild Start
            IF @vReorganizeRebuildFlag IN (1,2)
            BEGIN
                INSERT INTO Sp_IndexRebuildLog
                    (
                    SchemaName
                   ,TableName
                   ,[Description]
                   ,MachineName
                   ,UserName
                   ,StartDate
                   ,StartTime
                   ,EndDate
                   ,EndTime
                   ,[Status]
                    )
                VALUES
                    (
                    @vSchemaName
                   ,@vTable
                   ,CASE @vReorganizeRebuildFlag
                        WHEN 1 THEN @vIndexName + ' (Reorganize)'
                        WHEN 2 THEN @vIndexName + ' (Rebuild)'
                    END
                   ,CAST(SERVERPROPERTY('MachineName') AS VARCHAR(255))
                   ,CAST(SUSER_SNAME() AS VARCHAR(255))
                   ,CAST(GETDATE() AS DATE)
                   ,CAST(GETDATE() AS TIME)
                   ,NULL
                   ,NULL
                   ,0
                    )
                   
                SET @vLogID = @@IDENTITY            
                   
            END

            --Reorganizing the index
            IF @vReorganizeRebuildFlag = 1
                BEGIN
               
                    SET @vSQL =         ''
                    SET @vSQL = @vSQL + 'ALTER INDEX ' + @vIndexName                                                    + CHAR(13)
                    SET @vSQL = @vSQL + 'ON [' + RTRIM(LTRIM(@vSchemaName)) + '].[' + RTRIM(LTRIM(@vTable)) + ']'       + CHAR(13)
                    SET @vSQL = @vSQL + 'REORGANIZE'                                                                    + CHAR(13)

                    EXEC (@vSQL)
                    --PRINT @vSQL
                                  
                END
          
            ELSE
          
            --Rebuilding the index
            IF @vReorganizeRebuildFlag = 2
                BEGIN
                   
                    SET @vSQL =         ''
                    SET @vSQL = @vSQL + 'ALTER INDEX ' + @vIndexName                                                    + CHAR(13)
                    SET @vSQL = @vSQL + 'ON [' + RTRIM(LTRIM(@vSchemaName)) + '].[' + RTRIM(LTRIM(@vTable)) + ']'       + CHAR(13)
                    SET @vSQL = @vSQL + 'REBUILD WITH'                                                                  + CHAR(13)
                    SET @vSQL = @vSQL + '   ('                                                                          + CHAR(13)
                    SET @vSQL = @vSQL + '   FILLFACTOR = ' + CAST(@vFillFactor AS VARCHAR(10))                          + CHAR(13)
                    SET @vSQL = @vSQL + '   )'                                                                          + CHAR(13)

                    EXEC (@vSQL)
                    --PRINT @vSQL

                END


            UPDATE
                Sp_IndexRebuildLog
            SET
                EndDate     = CAST(GETDATE() AS DATE)
               ,EndTime     = CAST(GETDATE() AS TIME)
               ,Status      = 1
            WHERE
                LogId = @vLogID


            UPDATE
                #FragmentedTableList
            SET
                IsProcessed = 1
            WHERE
                TableName = @vTable
                AND
                IndexName = @vIndexName
           
        END

        DROP TABLE #FragmentedTableList

    END TRY

    BEGIN CATCH
        PRINT 'DATE : '             + CONVERT(VARCHAR, GETDATE()) + ' There is some run time exception.'
        PRINT 'ERROR CODE : '       + CONVERT(VARCHAR, ERROR_NUMBER())
        PRINT 'ERROR MESSAGE : '    + ERROR_MESSAGE()
    END CATCH

END

GO