如何使用单个查询删除除主键之外的所有索引 [英] How to drop all of the indexes except primary keys with single query

问题描述

我打算删除除主键之外的所有索引。我自己制作了主键，但所有其他索引都是SQL Server的建议。

I am planning to drop all of the indexes except the primary keys. I made the primary keys myself but all other indexes was suggestion of SQL Server.

删除所有非主键的索引后，计划使用SQL Server探查器调整模板进行数据库优化顾问并创建索引。

After dropping all indexes which are not primary keys, planning to use SQL Server profiler tuning template for database tuning advisor and create indexes.

用这种方式计划没有未使用的索引或性能降级索引。

With this way planning to not have unused indexes or performance degrading indexes.

这有多合乎逻辑？
谢谢。

How logical is this ? Thank you.

推荐答案

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'DROP INDEX ' 
    + QUOTENAME(SCHEMA_NAME(o.[schema_id]))
    + '.' + QUOTENAME(o.name) 
    + '.' + QUOTENAME(i.name) + ';'
    FROM sys.indexes AS i
    INNER JOIN sys.tables AS o
    ON i.[object_id] = o.[object_id]
WHERE i.is_primary_key = 0
AND i.index_id <> 0
AND o.is_ms_shipped = 0;

PRINT @sql;
-- EXEC sp_executesql @sql;

这篇关于如何使用单个查询删除除主键之外的所有索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！