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.

Solution 1

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

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

SELECT @sql = @sql + 
'ALTER INDEX ' + sys.indexes.name + ' ON  ' + sys.objects.name + ' 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.

Solution 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

Solution 3

Using a cursor to script things is more idiomatic than a temp table (and slightly briefer). To re-enable the indexes, replace DISABLE with REBUILD.

DECLARE cur_indexes CURSOR FOR
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 @TblName NVARCHAR(255), @IdxName NVARCHAR(255)

DECLARE @SQL NVARCHAR(MAX)

open cur_indexes
fetch next from cur_indexes into @TblName, @IdxName

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @SQL = N'ALTER INDEX ' + @IdxName + N' ON ' + @TblName + ' DISABLE;'

    EXEC sp_sqlexec @SQL    

    fetch next from cur_indexes into @TblName, @IdxName
END

close cur_indexes
deallocate cur_indexes

Comments

[Philip Kelley]

But put it in some form of loop (cursor or temp table/WHILE construct), to process each index in turn

[Daniel Quinlan]

Good point, but is disabling non-clustered indexes so expensive that it would be necessary?

[spender]

This only pulls the last result in the set. The declare should be DECLARE @sql AS VARCHAR(MAX)=''; and the select should start SELECT @sql = @sql+'ALTE... to concatenate all the results together. Otherwise, very nice. I'm going to try this now.

[JNK]

Disabling a non-clustered index keeps the definition and allows recreate/rebuild about 50% faster the dropping and creating.

[Zack]

You're missing the CURSOR keyword in line 1 I believe. DECLARE cur_indexes CURSOR FOR

[TarasB]

This script is great. It's maybe a different versions of SQL Server. But indexName and tableName do not work for me: Invalid column name 'indexName'. So I used sys.indexes.name and sys.objects.name. I'm using SQL 2012.

[Daniel Quinlan]

Thanks @TarasB, I wrote this in 2010 when I was working on a project in MS-SQL 2005. I don't have 2012 in front of me, but you might want to make sure name is unique and that the join will work. Maybe the object_id has been renamed to id or something similar.

[jumxozizi]

Please quote the object names before someone creates a table called "...drop database..." or something. 'ALTER INDEX ' + quotename(sys.indexes.name, '"') + ' ON ' + quotename(sys.objects.name, '"') + ' DISABLE;'.

[Daniel Quinlan]

@Rubiksmomo, well yeah, but aren't you describing a scenario with a rogue db-admin? If someone can already create tables with malicious names, I doubt quoting will save you from the damage they can do.

[jumxozizi]

Yeah, I'm not too worried about the SQL injection. It's just a habit to always block it. It would however be nice if the script worked even when you have an object with some special characters (like space) in the name.