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.

You might be better off just dropping them as there is no ALTER INDEX ENABLE, they would need to be recreated. They won't be maintained if disabled. — jl., Mar 25 '10 at 18:13
If dropped, you lose the definitions, and will have to remember them if (when) you need to recreate them. If disabled, the definitions will be persisted in the system tables, and you don't have to worry about recreating them from scratch. — Philip Kelley, Mar 25 '10 at 23:15
@jl you can re-enable the index by using ALTER INDEX REBUILD. — kristianp, Mar 20 '14 at 01:18

score 38 · Accepted Answer · edited May 27 '15 at 14:39

38

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.

edited May 27 '15 at 14:39

Gordon Bell

13,337
3
45
64

answered Mar 25 '10 at 18:11

Daniel Quinlan

2,639
1
20
23

But put it in some form of loop (cursor or temp table/WHILE construct), to process each index in turn – Philip Kelley Mar 25 '10 at 18:14
Good point, but is disabling non-clustered indexes so expensive that it would be necessary? – Daniel Quinlan Mar 25 '10 at 18:27
1

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. – spender Mar 25 '10 at 18:28
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. – TarasB May 13 '15 at 15:30
1

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. – Daniel Quinlan May 15 '15 at 10:33
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;'`. – jumxozizi Jun 12 '17 at 15:38
@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. – Daniel Quinlan Jun 13 '17 at 09:50
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. – jumxozizi Jun 14 '17 at 10:23

score 3 · Answer 2 · answered Mar 25 '10 at 18:20

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

kristianp · Answer 3 · 2014-06-21T01:51:04.450

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

You're missing the `CURSOR` keyword in line 1 I believe. `DECLARE cur_indexes CURSOR FOR` — Zack, Jun 19 '14 at 15:58

score 0 · Answer 4 · answered Aug 08 '10 at 14:51

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.

Disabling a non-clustered index keeps the definition and allows recreate/rebuild about 50% faster the dropping and creating. — JNK, Nov 30 '10 at 14:40

score 0 · Answer 5 · answered Jan 29 '20 at 11:23

To disable only the unique non clustred index

DECLARE @EnableOrRebuild as nvarchar(20)
SET @EnableOrRebuild = 'DISABLE'
/* SET @EnableOrRebuild = 'REBUILD' */ -- Uncomment for REBUILD
DECLARE @TableName as nvarchar(200) = 'ChorusDestinataire' -- Enter your table name here
DECLARE @SchemaName as nvarchar(200) = 'dbo' -- Enter the schema here
DECLARE @Sql as nvarchar(2000)=''

SELECT @Sql = @Sql + N'ALTER INDEX ' + quotename(i.name) + N' ON ' + quotename(s.name) + '.' + quotename(o.name) + ' ' + @EnableOrRebuild + N';' + CHAR(13) + CHAR(10)
  FROM sys.indexes i
 INNER JOIN sys.objects o ON i.object_id = o.object_id
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 WHERE i.type_desc = N'NONCLUSTERED'
   AND i.ignore_dup_key = 1
   AND o.type_desc = N'USER_TABLE'
   AND o.name = @TableName 
   AND s.name = @SchemaName

 -- SELECT @Sql;
EXEC (@Sql);

Disable all non-clustered indexes

5 Answers5

Linked