CREATE PROCEDURE [dbo].[usp_Process_NCIndexes_On_Table]
--EXEC dbo.usp_Process_NCIndexes_On_Table @Table_Name = 'sysdiagrams', @Schema_Name = 'dbo', @Action_Type = 'disable'
@Action_Type AS NVARCHAR(MAX),
@Table_Name AS NVARCHAR(MAX),
@Schema_Name AS NVARCHAR(10)

AS
BEGIN
-- ###########################
IF NOT (LOWER(@Action_Type) = 'enable' OR LOWER(@Action_Type) = 'disable')
	BEGIN
		SELECT 'Action type provided incorrectly. Please call the procedure either with ''enable'' or ''disable'' parameter'
		GOTO BRANCH_END
	END
-- ###########################
	
DECLARE @SQLCMD AS NVARCHAR(MAX)
DECLARE @Final_SQL AS NVARCHAR(MAX)
DECLARE @Index_Name AS NVARCHAR(2000)

--DECLARE @Table_Name AS NVARCHAR(100)
--SET @Table_Name = 'sysdiagrams'
--DECLARE @Schema_Name AS NVARCHAR(10)
--SET @Schema_Name = 'dbo'

SET @SQLCMD = 'DECLARE indexes_cursor CURSOR READ_ONLY FOR 
SELECT i.name AS [Index Name]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
WHERE i.type = 2 --NC INDEX TYPE
AND tbl.name = ''' + @Table_Name + ''' AND schema_name(schema_id) = ''' + @Schema_Name + ''''

EXEC sp_executesql @SQLCMD

IF @Action_Type = 'enable' 
	BEGIN
		GOTO BRANCH_ENABLE
	END
ELSE
	BEGIN
		GOTO BRANCH_DISABLE
	END
	
-- ############
BRANCH_DISABLE:
OPEN indexes_cursor
FETCH NEXT FROM indexes_cursor INTO @Index_Name

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Final_SQL = 'ALTER INDEX [' + @Index_Name + '] ON ' + '[' + @Schema_Name + '].[' + @Table_Name + ']' + ' DISABLE'
		--EXEC @Final_SQL
		PRINT @Final_SQL
			FETCH NEXT FROM indexes_cursor INTO @Index_Name
	END

CLOSE indexes_cursor
DEALLOCATE indexes_cursor
GOTO BRANCH_END

-- ############
BRANCH_ENABLE:
OPEN indexes_cursor
FETCH NEXT FROM indexes_cursor INTO @Index_Name

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Final_SQL = 'ALTER INDEX [' + @Index_Name + '] ON ' + '[' + @Schema_Name + '].[' + @Table_Name + ']' + ' REBUILD WITH (FILLFACTOR=100, PAD_INDEX=ON)'
		--EXEC @Final_SQL
		PRINT @Final_SQL
			FETCH NEXT FROM indexes_cursor INTO @Index_Name
	END

CLOSE indexes_cursor
DEALLOCATE indexes_cursor
GOTO BRANCH_END

BRANCH_END:
	PRINT 'Done'
END
GO