Disable all non-clustered indexes

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 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.

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

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

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.

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);