Disable all non-clustered indexes

Question

I select a number of non-clustered indexes from my database with the following:

SELECT  sys.objects.name tableName,
        sys.indexes.name indexName
FROM    sys.indexes
        JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'
        AND sys.objects.type_desc = 'USER_TABLE'

I'd like to run the following over each of the results:

ALTER INDEX indexName ON tableName DISABLE

How would I go about doing this? Is there a better way?

EDIT

I'm doing this for the purpose of truncating tables, then rebuilding with "ALTER INDEX bla ON table REBUILD". This needs to be automated, so dropping and rebuilding would be a somewhat higher maintenance activity I'd rather avoid. Is this a bad plan? I need a means of emptying tables with minimum overhead.

Answer 1

You can build the queries into a select statement, like so:

DECLARE @sql AS VARCHAR(MAX)='';

SELECT @sql = @sql + 
'ALTER INDEX ' + indexName + ' ON  ' + tableName + ' DISABLE;' +CHAR(13)+CHAR(10)
FROM 
    sys.indexes
JOIN 
    sys.objects 
    ON sys.indexes.object_id = sys.objects.object_id
WHERE sys.indexes.type_desc = 'NONCLUSTERED'
  AND sys.objects.type_desc = 'USER_TABLE';

EXEC(@sql);

Chars 13 and 10 are the line-feed/carriage-returns, so you can check the output by replacing EXEC with PRINT, and it will be more readable.

Answer 2

Build a table variable with the indexes and table names. Use a loop to iterate over them, and execute a dynamic SQL statement for each of them.

declare @Indexes table
(
    Num       int identity(1,1) primary key clustered,
    TableName nvarchar(255),
    IndexName nvarchar(255)
)

INSERT INTO @Indexes
(
    TableName,
    IndexName
)
SELECT  sys.objects.name tableName,
        sys.indexes.name indexName
FROM    sys.indexes
        JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'
        AND sys.objects.type_desc = 'USER_TABLE'

DECLARE @Max INT
SET @Max = @@ROWCOUNT

SELECT @Max as 'max'
SELECT * FROM @Indexes

DECLARE @I INT
SET @I = 1

DECLARE @TblName NVARCHAR(255), @IdxName NVARCHAR(255)

DECLARE @SQL NVARCHAR(MAX)

WHILE @I <= @Max
BEGIN
    SELECT @TblName = TableName, @IdxName = IndexName FROM @Indexes WHERE Num = @I
    SELECT @SQL = N'ALTER INDEX ' + @IdxName + N' ON ' + @TblName + ' DISABLE;'

    EXEC sp_sqlexec @SQL    

    SET @I = @I + 1

END

Answer 3

OTOH it might be better to DROP rather that DISABLE (or is it a petty lil syntactic diff between Oracle and MS SQL? :-) The reason I mention is that I remember tables that were repopulated and excplicilty denormalized twice a day and we were DROP-ing all indexes in order to force DB to rebuild both indexes and sproc execution plans after we loaded new date and rebuild all indexes.

Of course we had separate script for that since once you drop them, indexes are not in system tables anymore.