删除特定数据库的所有索引 [英] Delete all indexes of specific database

问题描述

如何从一个数据库中删除所有索引，无论是聚簇还是非聚簇？

How to delete all indexes from one database, whether clustered or nonclustered?

我需要使用脚本，而不是GUI。

I need do that with script, not over GUI.

已编辑

数据库有7个表，其中一些是查找，有些是通过外键相关的。每个表都有一个最小的索引，在创建主键时及时创建，因此自动创建了约束。通过GUI删除这些索引时，我收到一个错误，即由于依赖其他键而无法删除索引。

Database has 7 tables, some of them are lookups, some are related over foreign keys. Every table has minimal one index, created in time the primary key was created, so automatically was created constraint. When deleting such indexes over an GUI, I got an error that indexes cannot be deleted because of dependency on other keys.

因此，我需要首先删除作为外键的索引键，然后删除通过主键创建的索引。

So, I need to first delete an indexes keys that are foreign keys, and then an indexes created over primary keys.

推荐答案

动态SQL的变化略有不同，但是它首先丢弃外键，然后是主键，然后是索引（非聚簇索引首先使您不转换为堆（它可以影响所有非聚集索引））。

Slightly different variation of dynamic SQL, however it drops foreign keys first, then primary keys, then indexes (non-clustered indexes first so that you don't convert to a heap (which can affect all the non-clustered indexes)).

USE specific_database;
GO

首先，删除所有外键：

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + N'ALTER TABLE ' 
  + QUOTENAME(OBJECT_SCHEMA_NAME([parent_object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([parent_object_id])) 
  + ' DROP CONSTRAINT ' + QUOTENAME(name) + ';
'
FROM sys.foreign_keys
WHERE OBJECTPROPERTY([parent_object_id], 'IsMsShipped') = 0;

EXEC sp_executesql @sql;

现在放弃主键：

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + N'ALTER TABLE '
  + QUOTENAME(OBJECT_SCHEMA_NAME([parent_object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([parent_object_id])) 
  + ' DROP CONSTRAINT ' + QUOTENAME(name) + ';
'
FROM sys.key_constraints 
WHERE [type] = 'PK'
AND OBJECTPROPERTY([parent_object_id], 'IsMsShipped') = 0;

EXEC sp_executesql @sql;

最后，索引，非群集优先：

And finally, indexes, non-clustered first:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + N'DROP INDEX ' 
  + QUOTENAME(name) + ' ON '
  + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
  + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ';
'
FROM sys.indexes 
WHERE index_id > 0
AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0
ORDER BY [object_id], index_id DESC;

EXEC sp_executesql @sql;

请注意，数据库引擎优化顾问会推荐一堆这些索引（根据你的工作量而定）提出它，可能会错过一些，并可能建议冗余和几乎重复的索引）。但是，它不会推荐您刚刚删除的任何数据完整性内容（PK，FK，唯一约束）。

Note that the database engine tuning advisor will recommend a bunch of these indexes (and depending on the workload you present it, may miss some, and may suggest redundant and nearly duplicate indexes). However it is not going to recommend any of the data integrity stuff you just deleted (PK, FK, unique constraints).

这篇关于删除特定数据库的所有索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！