

CREATE PROCEDURE [dbo].[usp_ETL_Index_SpecificTable_ForceMaintenance]

 @Action		VARCHAR(7)
,@IndexType		VARCHAR(12)
,@TableName		VARCHAR(50)

as

--************************************************************************
-- Description: This disables or rebuilds an index (either clustered or
--              nonclustered) for a specific table
--
-- WARNING: !! This uses dynamic sql - therefore VALIDATE the text going in !!
--
-- By accessing or using this code, you agree to be bound by the following: http://sql313.com/index.php/license
-- Copyright (c) 2014, Glenda Gable
-- All rights reserved.
--
-- For more information, check out my blog at http://sql313.com 
-- You can contact me at: ggable@sql313.com
-- To contribute code and see your name in the change log, email your improvements & ideas to help@sql313.com.
--
--
--
-- Note: If all Clustered Indexes are disabled, then all NonClustered Indexes are also disabled
--
-- @Action expects: 'REBUILD' or 'DISABLE'
-- @IndexType expects: 'CLUSTERED' or 'NONCLUSTERED'
-- @TableName expects the actual name of the table
--
-- Vers Who Date        What
-- ---- --- ----------- -------------------------
-- 1.0  gmg 30-Jul-2014 Initial Release
--************************************************************************

/*
-- debugging only
DECLARE @Action		VARCHAR(7) = 'DISABLE'
	,@IndexType		VARCHAR(12) = 'NONCLUSTERED'
	,@TableName		VARCHAR(50) = 'PT_STATUS_INS'
*/


DECLARE @li_error			INT = 0
	,@lv_errorMsg			VARCHAR(4000) = NULL
	,@lb_tranStartedHere	BIT = 0
DECLARE @dynamicSQL			VARCHAR(MAX) = '';

-- if there is not already a transaction, start one 
-- and indicate it started in this proc
if @@TRANCOUNT = 0 begin
	begin tran
	set @lb_tranStartedHere = 1
END

-- -------------------------------------------------------------------
-- Validate data entered
SET @Action = UPPER(@Action)
SET @IndexType = UPPER(@IndexType)

IF @Action IS NULL BEGIN
	SET @li_error = -991
	SET @lv_errorMsg = CASE WHEN @lv_errorMsg IS NULL THEN '' ELSE @lv_errorMsg + '; ' END + 'ERROR ' + CAST(@li_error AS VARCHAR) + ' param @Action is null and value is required'
END
IF @Action NOT IN ('DISABLE','REBUILD') BEGIN
	SET @li_error = -992
	SET @lv_errorMsg = CASE WHEN @lv_errorMsg IS NULL THEN '' ELSE @lv_errorMsg + '; ' END + 'ERROR ' + CAST(@li_error AS VARCHAR) + ' invalid value passed as param @Action = ' + @Action
END

IF @IndexType IS NULL BEGIN
	SET @li_error = -981
	SET @lv_errorMsg = CASE WHEN @lv_errorMsg IS NULL THEN '' ELSE @lv_errorMsg + '; ' END + 'ERROR ' + CAST(@li_error AS VARCHAR) + ' param @IndexType is null and value is required'
END
IF @IndexType NOT IN ('CLUSTERED','NONCLUSTERED') BEGIN
	SET @li_error = -982
	SET @lv_errorMsg = CASE WHEN @lv_errorMsg IS NULL THEN '' ELSE @lv_errorMsg + '; ' END + 'ERROR ' + CAST(@li_error AS VARCHAR) + ' invalid value passed as param @IndexType = ' + @IndexType
END

IF @TableName IS NULL BEGIN
	SET @li_error = -971
	SET @lv_errorMsg = CASE WHEN @lv_errorMsg IS NULL THEN '' ELSE @lv_errorMsg + '; ' END + 'ERROR ' + CAST(@li_error AS VARCHAR) + ' param @TableName is null and value is required'
END
IF NOT EXISTS (SELECT * FROM sys.tables AS t WHERE t.name = @TableName) BEGIN
	SET @li_error = -972
	SET @lv_errorMsg = CASE WHEN @lv_errorMsg IS NULL THEN '' ELSE @lv_errorMsg + '; ' END + 'ERROR ' + CAST(@li_error AS VARCHAR) + ' invalid value passed as param @TableName = ' + @TableName
END


-- if no errors so far, check to make sure the index(es) exist
IF @li_error = 0 AND @lv_errorMsg IS NULL BEGIN 
	IF NOT EXISTS (SELECT * 
				   FROM sys.indexes I 
				   INNER JOIN sys.tables T ON I.object_id = T.object_id
				   WHERE 1=1
					 AND I.type_desc = @IndexType
					 AND I.name IS NOT NULL
					 --AND I.is_disabled = 0
					 AND t.name = @TableName
				   ) BEGIN
		SET @li_error = -891
		SET @lv_errorMsg = CASE WHEN @lv_errorMsg IS NULL THEN '' ELSE @lv_errorMsg + '; ' END + 'ERROR ' + CAST(@li_error AS VARCHAR) + ' No ' + @IndexType + ' index(es) exists for the table ' + @TableName
	END
END
-- we checked if the index(es) exist, now check if they are ALL already disabled
IF @li_error = 0 AND @lv_errorMsg IS NULL BEGIN 
	IF NOT EXISTS (SELECT * 
				   FROM sys.indexes I 
				   INNER JOIN sys.tables T ON I.object_id = T.object_id
				   WHERE 1=1
					 AND I.type_desc = @IndexType
					 AND I.name IS NOT NULL
					 --AND I.is_disabled = 1
					 AND (
					      (@Action = 'DISABLE' AND I.is_disabled = 0)
					      OR
					      (@Action = 'REBUILD' AND I.is_disabled = 1)
					 )
					 AND t.name = @TableName
				   ) BEGIN
		SET @li_error = -891
		SET @lv_errorMsg = CASE WHEN @lv_errorMsg IS NULL THEN '' ELSE @lv_errorMsg + '; ' END + 'ERROR ' + CAST(@li_error AS VARCHAR) + ' ALL ' + @IndexType + ' index(es) are already disabled for the table ' + @TableName
	END
END





-- -------------------------------------------------------------------
-- Actual guts of the stored procedure

-- only build the sql if there arent any errors so far
IF @li_error = 0 AND @lv_errorMsg IS NULL BEGIN 
	SELECT @dynamicSQL = @dynamicSQL + 'ALTER INDEX [' + I.name + '] ON [' + T.name + '] ' + @Action + ';' + CHAR(13) + CHAR(10)
	FROM sys.indexes I
	INNER JOIN sys.tables T ON I.object_id = T.object_id
	WHERE 1=1
	  AND I.type_desc = @IndexType
	  AND I.name IS NOT NULL
	  AND I.is_disabled = CASE WHEN @Action = 'REBUILD' THEN 1 ELSE 0 END
	  AND t.name = @TableName
	SET @li_error = @@ERROR;
END

-- just checking 1 more time for errors before executing sql statement
IF @li_error = 0 AND @lv_errorMsg IS NULL BEGIN 
    EXEC (@dynamicSQL);
    --SELECT @dynamicSQL
END





-- -------------------------------------------------------------------
-- if there is a transaction and it started here, finish it here
-- if it didnt start here, let the 
if @@trancount > 0 and @lb_tranStartedHere = 1 begin
	-- if there is an error, rollback, otherwise, commit
	if @li_error <> 0 or @lv_errorMsg is not null begin
		rollback tran
	end
	else begin
		commit tran
	end
end



-- -------------------------------------------------------------------
-- Raise Error if applicable
IF @li_error <> 0 OR @lv_errorMsg IS NOT NULL BEGIN 
	RAISERROR (@lv_errorMsg, -- Message text.
           16, -- Severity,
           1 -- State,
           );
END



