SP with input parameters to disable and enable indexes

To make a table as an input for a stored procedure, you need to create a table type like below.

CREATE TYPE dbo.reftable AS TABLE
(
tablename varchar(30),
schemaname varchar(10),
flag bit
)

Assuming you are planning to disable all indexes on the table(since you have no column fields specifically, you can use a cursor to loop through or a while statement also can do. To use a while statement, add an id column to your table so that it is easier and faster. You can also a STUFF to build it. Since you asked exclusively for a loop, I have written some code using a cursor.

Proc is created using cursor and dynamic sql that uses a table as a input, as below

CREATE PROCEDURE usp_indexes
(
@ref reftable READONLY
)
AS
BEGIN

DECLARE @tblnm VARCHAR(30), @schnm VARCHAR(10), @flag BIT, @sql VARCHAR(MAX)

DECLARE cursor_index CURSOR
FOR SELECT tablename, schemaname, flag FROM @ref

OPEN cursor_index
FETCH NEXT FROM cursor_index INTO @tblnm, @schnm, @flag

WHILE @@FETCH_STATUS = 0
BEGIN
        
        SET @sql = 'ALTER INDEX ALL ON '+@schnm+'.'+@tblnm+' DISABLE;'
        sp_execute (@sql)
        
FETCH NEXT FROM cursor_index INTO @tblnm, @schnm, @flag
END
CLOSE cursor_index
DEALLOCATE cursor_index

END

To execute the proc use below..

DECLARE @ref reftable
INSERT INTO @ref
SELECT * FROM testingindex

EXEC usp_indexes @ref

Once you have data in your ref table it should work fine. I hope you truncate the table everytime before you run the operation because if you have historical data, it will start disabling for everything in the table since there are no restrictions specified.

Please read the Limitations and Restrictions before disabling indexes.

Please mark as answer if this code has helped you achieve your goal.

Good Luck :)

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to's.