rebuild index

USE DBName
GO

--DROP PROC spRebuildIndex

CREATE PROC spRebuildIndex
    @tableName VARCHAR(100) = NULL ,
    @indexName VARCHAR(200) = NULL
AS 
    BEGIN 

--DECLARE @tableName VARCHAR(100),@indexName VARCHAR(200)
        SELECT  @tableName = NULL ,
                @indexName = NULL

        DECLARE @objName VARCHAR(200) ,
            @ixName VARCHAR(200) ,
            @fragPercent FLOAT ,
            @sql VARCHAR(MAX)
        SET @sql = ''

        DECLARE @indexId INT ,
            @objectId INT
        SELECT  @objectId = object_id ,
                @indexId = index_id
        FROM    sys.indexes
        WHERE   name = @indexName
                AND object_id = OBJECT_ID(@tableName)



        DECLARE cur CURSOR FORWARD_ONLY
        FOR
            SELECT  OBJECT_NAME(a.object_id) ,
                    b.name ,
                    avg_fragmentation_in_percent
            FROM    sys.dm_db_index_physical_stats(DB_ID(), @objectId,
                                                   @indexId, NULL, NULL) a
                    INNER JOIN sys.indexes b ON a.object_id = b.object_id
                                                AND a.index_id = b.index_id
            WHERE   a.index_id != 0
                    AND a.avg_fragmentation_in_percent > 5


        OPEN cur
        FETCH NEXT FROM cur INTO @objName, @ixName, @fragPercent

        WHILE @@fetch_status = 0 
            BEGIN 
  
                IF @fragPercent > 5
                    AND @fragPercent <= 30 
                    BEGIN 
                        SET @sql = 'alter index ' + @ixName + ' on '
                            + @objName + ' REORGANIZE'
                        EXEC(@sql)
                        PRINT ( @objName + ':' + @ixName+' REORGANIZE' )
                    END
                IF @fragPercent > 30 
                    BEGIN 
                        SET @sql = 'alter index ' + @ixName + ' on '
                            + @objName + ' REBUILD'
                        EXEC(@sql)
                         PRINT ( @objName + ':' + @ixName+' REBUILD' )
                    END
  --PRINT(@sql)
              

                FETCH NEXT FROM cur INTO @objName, @ixName, @fragPercent

            END

        CLOSE cur
        DEALLOCATE cur

    END