禁用所有非聚集索引 [英] 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?

编辑

我这样做是为了截断表的目的，然后用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.

这篇关于禁用所有非聚集索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！