如何禁用并重新启用SQLServer数据库中的所有索引？

最新推荐文章于 2024-07-18 17:19:00 发布

养加虎口

最新推荐文章于 2024-07-18 17:19:00 发布

阅读量898

点赞数

本文链接：https://blog.csdn.net/qq49846460/article/details/109461420

版权

禁用脚本

SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' +  QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' DISABLE' 
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 0

启用脚本(重建)

SELECT 'ALTER INDEX ' + QUOTENAME(I.name) + ' ON ' +  QUOTENAME(SCHEMA_NAME(T.schema_id))+'.'+ QUOTENAME(T.name) + ' REBUILD' 
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
WHERE I.type_desc = 'NONCLUSTERED'
AND I.name IS NOT NULL
AND I.is_disabled = 1