/****** Object:  StoredProcedure [dbo].[LT_SYS_RebuildIndex]    Script Date: 21/05/2024 11:51:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LT_SYS_RebuildIndex]
       @FragmentationPercent INT = 20
      ,@Rebuild              BIT = 1
      ,@ReOrganise           BIT = 1
	  ,@ShrinkDB			 BIT = 1
	  ,@RebuildWithOnLine	 BIT = 1
AS
BEGIN
/* =============================================
    Author:		Sanjay Singh
    Create date: 22-12-2011
    Description:	Rebuild Index
    Schedule this procedure when no users are using the database
    Dependencies -------------------------------------------------------
    SQL JOB -> Processes Menu
	EXEC LT_SYS_RebuildIndex @FragmentationPercent = 20, @Rebuild = 0, @ReOrganise = 0
    Business Rules-----------------------------------------------------
    MODIFICATION HISTORY ------------------------
    DATE		AUTHOR	REFERENCE	COMMENTS
    13-01-2021	Sanjay 	12.30	Performing Rebuild after Reoganise does not fully defragment. Changed order to Rebuild then Reoganise
	28/07/2023	Sanjay	14.91	Added logging and parameterise to allow viewing data and changed default to 20%
	20/11/2023	Sanjay	15.10	Add Shrink Database option. Changed default fragmentation percent to 20
    ============================================= */
    SET NOCOUNT ON;

	DECLARE @sMsg VARCHAR(MAX)
		   ,@sModule VARCHAR(255) = OBJECT_NAME(@@PROCID)
		   ,@sStatus VARCHAR(50)

		   ,@DateStart DATETIME = GETDATE()
		   ,@DateFinish DATETIME
		   ,@NumberOfIndexesFragmented INT = 0

    DECLARE @tables TABLE
       (
           ID                   INT IDENTITY(1,1)
          ,SchemaName           SYSNAME
          ,TableName            SYSNAME
          ,IndexName            SYSNAME
          ,IndexType            VARCHAR(50)
          ,FragmentationPercent DECIMAL(12,2)
       )

	BEGIN TRY

		INSERT INTO @tables
		   (
			 SchemaName
			,TableName
			,IndexName
			,IndexType
			,FragmentationPercent
		   )
		SELECT
			 Schema_Name(t.schema_id)
			,Object_Name(ind.OBJECT_ID) AS TableName
			,ind.name AS                   IndexName
			,indexstats.index_type_desc AS IndexType
			,indexstats.avg_fragmentation_in_percent
		FROM sys.dm_db_index_physical_stats(Db_Id(),NULL,NULL,NULL,NULL) AS indexstats
		JOIN sys.indexes AS ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
		JOIN sys.tables AS t ON ind.object_id = t.object_id
		WHERE indexstats.avg_fragmentation_in_percent > @FragmentationPercent AND ind.name IS NOT NULL
		ORDER BY indexstats.avg_fragmentation_in_percent DESC

		SELECT @NumberOfIndexesFragmented = Count(*) FROM @tables

		DECLARE @Id     INT
			   ,@Sql    NVARCHAR(MAX)
			   ,@Schema SYSNAME
			   ,@Table  SYSNAME
			   ,@Index  SYSNAME

		SELECT @Id = Min(ID) FROM @Tables

		IF @Rebuild = 1 OR @ReOrganise = 1
		BEGIN
			WHILE @Id IS NOT NULL
			BEGIN
				SELECT
					 @Schema = SchemaName
					,@Table = TableName
					,@Index = IndexName 
				FROM @Tables 
				WHERE ID = @Id

				--Rebuild
				IF @Rebuild = 1
				BEGIN
					IF @RebuildWithOnLine = 1
					BEGIN
						SET @Sql = 'ALTER INDEX [' + @Index + '] ON [' + @Schema + '].[' + @Table + '] REBUILD'
					END
					ELSE
					BEGIN
						SET @Sql = 'ALTER INDEX [' + @Index + '] ON [' + @Schema + '].[' + @Table + '] REBUILD WITH(ONLINE = OFF)'
					END
					
					EXEC dbo.LT_SYS_ExecuteScript @script = @Sql, @RAISERROROnFailure = 0
				END

				--Reoganise
				IF @ReOrganise = 1
				BEGIN
					SET @Sql = 'ALTER INDEX [' + @Index + '] ON [' + @Schema + '].[' + @Table + '] REORGANIZE WITH(LOB_COMPACTION = ON)'
					EXEC dbo.LT_SYS_ExecuteScript @script = @Sql, @RAISERROROnFailure = 0
				END

				DELETE @Tables WHERE ID = @Id
				SET @Id = NULL
				SELECT @Id = Min(ID) FROM @Tables
			END
	
		END

		--Shrink Log. DBCC SHRINKDATABASE ([DEV-LinkSOFT], 10);
		IF @ShrinkDB = 1   
		BEGIN
			SET @Sql = 'DBCC SHRINKDATABASE ([' + DB_NAME() + '])'
			EXEC dbo.LT_SYS_ExecuteScript @script = @Sql, @RAISERROROnFailure = 0
		END


		SET @sMsg = dbo.LT_SYS_StringFormat_fx5('"Rebuild Index" was run using these parameters: Fragmentation Percent: {0}, Rebuild: {1}, Reorganise: {2}. ShrinkDB: {3}. Number of fragmented indexes found: {4}'
			,@FragmentationPercent
			,dbo.LT_SYS_ConvertBitToYN_fx(@Rebuild)
			,dbo.LT_SYS_ConvertBitToYN_fx(@ReOrganise)
			,dbo.LT_SYS_ConvertBitToYN_fx(@ShrinkDB)
			,@NumberOfIndexesFragmented)

		SET @DateFinish = GETDATE()

		EXEC LT_SYS_Log_Event
				@sModule = @sModule,
				@sMsg = @sMsg,
				@DateStarted = @DateStart,
				@DateFinished = @DateFinish

	END TRY
	BEGIN CATCH			
		DECLARE @ERROR_SEVERITY INT = ERROR_SEVERITY()
		SET @sMsg = ERROR_MESSAGE()
		SET @sStatus = CASE WHEN ISNULL(@sStatus,'') = '' THEN 'Error' ELSE @sStatus END
	   
		EXEC [dbo].[LT_SYS_Log_INSERT]
			@sModule = @sModule,
			@sMsg = @sMsg,
			@sStatus= @sStatus,
			@ERROR_SEVERITY=@ERROR_SEVERITY
        
		RAISERROR(@sMsg,11,1)			
	END CATCH
END