INDEX - REBUILD / REINDEX

USE TestDB

GO 

SET NOCOUNT ON

DECLARE @Index_Name NVARCHAR(150), @FragPercent FLOAT, @RowCount INT, @ExecCmd NVARCHAR(500) = '', @EntityName NVARCHAR(100) 

SET @EntityName = 'TABLENAME' 

DECLARE @IndexInfo TABLE (RwNo INT IDENTITY(1,1), Index_ID INT , Index_Name NVARCHAR(150), FragmentationPercentage FLOAT)  

INSERT INTO @IndexInfo

SELECT a.index_id, name, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@EntityName), NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.OBJECT_ID = b.OBJECT_ID AND a.index_id = b.index_id

SET @RowCount = (SELECT COUNT(*) from @IndexInfo) 

--SELECT * FROM @IndexInfo 

WHILE ( @RowCount > 0)

BEGIN

SELECT @Index_Name = Index_Name, @FragPercent = FragmentationPercentage FROM @IndexInfo WHERE RwNo = @RowCount 

SELECT @ExecCmd = CASE WHEN @FragPercent > 5 AND @FragPercent <= 40 THEN 'ALTER INDEX ' + @Index_Name + ' ON ' + @EntityName + ' REORGANIZE'

                                WHEN @FragPercent > 40  THEN 'ALTER INDEX ' + @Index_Name + ' ON ' + @EntityName + ' REBUILD'

                                --ELSE ''

                                END

EXEC (@ExecCmd)

SET @RowCount = @RowCount - 1

END

-- REBUILD ALL

DECLARE @DatabaseName SYSNAME   = DB_NAME(),  @TableName VARCHAR(256) 

DECLARE @FILLFACTOR INT = 85

DECLARE @DynamicSQL NVARCHAR(max) =

 'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +

 ''.'' + TABLE_NAME AS TABLENAME  

 FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE

 TABLE_TYPE = ''BASE TABLE'''    

BEGIN 

  EXEC sp_executeSQL @DynamicSQL  -- create tables cursor

  OPEN curAllTablesInDB  

  FETCH NEXT FROM curAllTablesInDB INTO @TableName  

  WHILE (@@FETCH_STATUS = 0) 

  BEGIN  

       SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +

         ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')'

       PRINT @DynamicSQL

       -- ALTER INDEX ALL ON Purchasing.ShipMethod REBUILD WITH (FILLFACTOR = 85)

       EXEC sp_executeSQL @DynamicSQL 

       FETCH NEXT FROM curAllTablesInDB INTO @TableName  

   END   -- cursor WHILE

   CLOSE curAllTablesInDB  

   DEALLOCATE curAllTablesInDB 

END

-- REINDEX ALL

CREATE PROC sprocDBIndexREBUILD @FILLFACTOR INT = 90

AS

BEGIN

DECLARE @DatabaseName SYSNAME   = DB_NAME(),  @TableName VARCHAR(256) 

DECLARE @DynamicSQL NVARCHAR(max) =

 'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +

 ''.'' + TABLE_NAME AS TABLENAME  

 FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE

 TABLE_TYPE = ''BASE TABLE'''  

BEGIN 

  EXEC sp_executeSQL @DynamicSQL  -- create tables cursor

  OPEN curAllTablesInDB  

  FETCH NEXT FROM curAllTablesInDB INTO @TableName  

  WHILE (@@FETCH_STATUS = 0) 

  BEGIN  

       SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +

         ' REBUILD WITH (ONLINE=ON, FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR)+ ')' 

       -- PRINT @DynamicSQL

       EXEC sp_executeSQL @DynamicSQL 

       FETCH NEXT FROM curAllTablesInDB INTO @TableName  

   END   -- cursor WHILE

   CLOSE curAllTablesInDB  

   DEALLOCATE curAllTablesInDB 

END

END -- sproc

GO