Toggle Indexes on an Object

The index companion to the FK toggler: this stored procedure disables (or re-enables via REBUILD) every non-primary-key, non-heap index on a table - with an optional comma-separated list of index names to exclude. Classic speed-up for bulk operations: disable indexes, do the work, re-enable.

Example: EXEC dbo.ToggleIndexesOnObject @ObjectName = 'dbo.Orders', @Enable = 0, @ExcludeIndexeNames = 'IX_Orders_CustomerID'

CREATE PROCEDURE [dbo].[ToggleIndexesOnObject]
(
	@ObjectName sysname,
	@Enable BIT,
	@ExcludeIndexeNames nvarchar(max) = NULL
) AS
BEGIN--PROCEDURE
	SET NOCOUNT ON;

	DECLARE @IndexName sysname
	DECLARE @tSQL varchar(max)
	
	DECLARE ObjectIndexs CURSOR FAST_FORWARD FOR
		SELECT
			name
		FROM
			sys.indexes
		WHERE
			object_id = object_id(@ObjectName)
			AND is_disabled = @Enable
			AND type_desc <> 'HEAP'
			AND name NOT IN (SELECT Value FROM dbo.SplitArray(@ExcludeIndexeNames, ','))
			AND is_primary_key <> 1
	OPEN ObjectIndexs
	
	FETCH FROM ObjectIndexs INTO @IndexName
	
	Print CASE WHEN @Enable = 0 THEN 'Disabling' ELSE 'Enabling' END + ' individual indexes on: ' + @ObjectName + '.'
	WHILE(@@FETCH_STATUS = 0)
	BEGIN--WHILE
		---------------------------------------------------------------------------------

		Print CASE WHEN @Enable = 0 THEN 'Disabling' ELSE 'Enabling' END + ' index: ' + @IndexName + '.'
		IF(@Enable = 1)
		BEGIN--IF
			SET @tSQL = 'ALTER INDEX [' + @IndexName + '] ON ' + @ObjectName + ' REBUILD'
		END ELSE BEGIN--IF
			SET @tSQL = 'ALTER INDEX [' + @IndexName + '] ON ' + @ObjectName + ' DISABLE'
			END--IF

		EXEC(@tSQL)

		---------------------------------------------------------------------------------
		FETCH FROM ObjectIndexs INTO @IndexName
	END--WHILE

	CLOSE ObjectIndexs DEALLOCATE ObjectIndexs

END--PROCEDURE