禁用所有非聚集索引

本文介绍了禁用所有非聚集索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我使用以下内容从我的数据库中选择了许多非聚集索引：

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?

编辑

我这样做是为了截断表的目的，然后用ALTER INDEX bla ON table REBUILD重建。这需要自动化，因此丢弃和重建将是一个更高的维护活动，我宁愿避免。这是一个糟糕的计划吗？我需要一种以最小的开销清空表的方法。

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.

推荐答案

您可以将查询构建到select语句中，如下所示： / p>

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

第13章和第10章是换行/回车，所以你可以检查输出用 PRINT 替换 EXEC ，它将更具可读性。

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.