Index fragmentation Reorganize & Rebuild

CREATE PROC [EBPROC_INDEX_MAINTENANCE] @DBName VARCHAR(100) 
--op  
--ındex reo reb frag 
--02.08.2017 
 
AS BEGIN 
 
        SET NOCOUNT ON; 
 
        DECLARE 
 
            @OBJECT_ID INT, 
 
            @INDEX_NAME sysname, 
 
            @SCHEMA_NAME sysname, 
 
            @OBJECT_NAME sysname, 
 
            @AVG_FRAG float, 
 
            @command varchar(8000), 
 
            @RebuildCount int, 
  
            @ReOrganizeCount int 
 
  
 
        CREATE TABLE #tempIM ( 
 
            [ID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY, 
 
            [INDEX_NAME] sysname NULL, 
 
            [OBJECT_ID] INT NULL, 
 
            [SCHEMA_NAME] sysname NULL, 
 
            [OBJECT_NAME] sysname NULL, 
 
            [AVG_FRAG] float 
 
        )       
 
        SELECT @RebuildCount=0,@ReOrganizeCount=0 
 
          
 
        --Get Fragentation values 
 
        SELECT @command= 
 
            'Use ' + @DBName + '; 
 
            INSERT INTO #tempIM (OBJECT_ID, INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG) 
 
            SELECT 
 
                ps.object_id, 
 
                i.name as IndexName, 
 
                OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName, 
 
                OBJECT_NAME (ps.object_id) as ObjectName, 
 
                ps.avg_fragmentation_in_percent 
 
            FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, ''LIMITED'') ps 
 
            INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id 
 
            WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0 
 
                and ps.database_id=DB_ID('''+@DBName+''') 
 
            ORDER BY avg_fragmentation_in_percent desc 
 
            ' 
 
          
 
        exec(@command) 
 
        DECLARE c CURSOR FAST_FORWARD FOR 
 
            SELECT OBJECT_ID,INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG 
 
            FROM #tempIM 
 
        OPEN c 
 
        FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG 
 
        WHILE @@FETCH_STATUS = 0 
 
        BEGIN 
 
            --Reorganize or Rebuild 
 
            IF @AVG_FRAG>30 BEGIN 
 
                SELECT @command = 'Use ' + @DBName + '; ALTER INDEX [' + @INDEX_NAME +'] ON [' 
 
                                  + @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] REBUILD   WITH (ONLINE = ON  )'; 
 
                SET @RebuildCount = @RebuildCount+1 
 
            END ELSE BEGIN 
 
                SELECT @command = 'Use ' + @DBName + '; ALTER INDEX [' + @INDEX_NAME +'] ON [' 
 
                                  + @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] REORGANIZE '; 
 
                SET @ReOrganizeCount = @ReOrganizeCount+1 
 
            END 
 
                                    
 
            BEGIN TRY 
 
                EXEC (@command);     
            END TRY 
   
            BEGIN CATCH 
 
            END CATCH 
 
              
 
            FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG 
 
        END 
 
        CLOSE c 
 
        DEALLOCATE c 
 
          
 
        DROP TABLE #tempIM 
 
          
 
        SELECT cast(@RebuildCount as varchar(5))+' index Rebuild,'+cast(@ReOrganizeCount as varchar(5))+' index Reorganize edilmistir.' as Result 
 
END