我使用以下内容从我的数据库中选择了许多非聚集索引:
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.
更多推荐
禁用所有非聚集索引
发布评论