如何使用一个命令删除 SQL 数据库中的所有索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1344401/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I drop all indexes in a SQL database with one command?
提问by djangofan
So, how can I drop all indexes in a SQL database with one command? I have this command that will get me all the 20 or so drop statements, but how can I run all of those drop statements from this "result set"?
那么，如何使用一个命令删除 SQL 数据库中的所有索引？我有这个命令可以让我获得所有 20 个左右的 drop 语句，但是如何从这个“结果集”运行所有这些 drop 语句？
select * from vw_drop_idnex;
Another variation that gives me the same list is:
另一个给我相同列表的变体是：
SELECT 'DROP INDEX ' + ix.Name + ' ON ' + OBJECT_NAME(ID) AS QUERYLIST
FROM sysindexes ix
WHERE ix.Name IS NOT null and ix.Name like '%pre_%'
I tried to do "exec(select cmd from vw_drop_idnex)" and it didn't work. I am looking for something that works like a for loop and runs the queries one by one.
我尝试执行“exec（从 vw_drop_idnex 中选择 cmd）”，但没有奏效。我正在寻找像 for 循环一样工作的东西，并逐个运行查询。
-----------------------
-----------------------
With Rob Farleys help, final draft of the script is:
在 Rob Farleys 的帮助下，剧本的最终稿是：
declare @ltr nvarchar(1024);
SELECT @ltr = ( select 'alter table '+o.name+' drop constraint '+i.name+';'
from sys.indexes i join sys.objects o on i.object_id=o.object_id
where o.type<>'S' and is_primary_key=1
FOR xml path('') );
exec sp_executesql @ltr;
declare @qry nvarchar(1024);
select @qry = (select 'drop index '+o.name+'.'+i.name+';'
from sys.indexes i join sys.objects o on i.object_id=o.object_id
where o.type<>'S' and is_primary_key<>1 and index_id>0
for xml path(''));
exec sp_executesql @qry
回答by Rob Farley
You're very close.
你很亲近。
declare @qry nvarchar(max);
select @qry =
(SELECT 'DROP INDEX [' + ix.name + '] ON ' + OBJECT_NAME(ID) + '; '
FROM sysindexes ix
WHERE ix.Name IS NOT null and ix.Name like '%prefix_%'
for xml path(''));
exec sp_executesql @qry
回答by Anatoli Klamer
this worked for me we skip sys indexes and for constraints
这对我有用，我们跳过系统索引和约束
declare @qry nvarchar(max);
select @qry = (
select 'IF EXISTS(SELECT * FROM sys.indexes WHERE name='''+ i.name +''' AND object_id = OBJECT_ID(''['+s.name+'].['+o.name+']'')) drop index ['+i.name+'] ON ['+s.name+'].['+o.name+']; '
from sys.indexes i
inner join sys.objects o on i.object_id=o.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
where o.type<>'S' and is_primary_key<>1 and index_id>0
and s.name!='sys' and s.name!='sys' and is_unique_constraint=0
for xml path(''));
exec sp_executesql @qry
回答by Tawani
From: Stephen Hill's Bloggie
来自：斯蒂芬希尔的博客
DECLARE @indexName VARCHAR(128)
DECLARE @tableName VARCHAR(128)
DECLARE [indexes] CURSOR FOR
SELECT [sysindexes].[name] AS [Index],
[sysobjects].[name] AS [Table]
FROM [sysindexes]
INNER JOIN [sysobjects]
ON [sysindexes].[id] = [sysobjects].[id]
WHERE [sysindexes].[name] IS NOT NULL
AND [sysobjects].[type] = 'U'
--AND [sysindexes].[indid] > 1
OPEN [indexes]
FETCH NEXT FROM [indexes] INTO @indexName, @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'DROP INDEX [' + @indexName + '] ON [' + @tableName + ']'
Exec ('DROP INDEX [' + @indexName + '] ON [' + @tableName + ']')
FETCH NEXT FROM [indexes] INTO @indexName, @tableName
END
CLOSE [indexes]
DEALLOCATE [indexes]
GO
回答by Martin Smith
None of the answers quite suited my needs.
没有一个答案非常适合我的需求。
I needed one that will also drop indexes that backup unique or primary constraints (except if these can't be dropped as they back up a foreign key)
我需要一个同时删除备份唯一约束或主要约束的索引（除非它们在备份外键时不能删除）
DECLARE @SqlScript NVARCHAR(MAX);
SELECT @SqlScript =
(
SELECT
'
BEGIN TRY
'+ CASE WHEN 1 IN (i.is_primary_key, i.is_unique_constraint) THEN
'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + QUOTENAME(i.name) + ';'
else
'
DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(t.name)
END+'
END TRY
BEGIN CATCH
RAISERROR(''Could not drop %s on table %s'', 0,1, ' + QUOTENAME(i.name, '''') + ', ' + QUOTENAME(t.name, '''') + ')
END CATCH
'
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc IN ('CLUSTERED', 'NONCLUSTERED' )
ORDER BY t.object_id, i.index_id DESC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');
--Return script that will be run
SELECT @SqlScript AS [processing-instruction(x)]
FOR XML PATH('');
EXEC (@SqlScript);
回答by Geoff Griswald
The "Final Draft" that OP posts as part of his question errors out if there are already zero indexes on any table in the DB. I needed to fix that.
如果数据库中的任何表上已经有零索引，OP 作为问题的一部分发布的“最终草案”就会出错。我需要解决这个问题。
Also, I wanted more control over the process than dropping all indexes on alltables, so I wrote the following stored proc to do it one table at a time:
另外，我想对过程比下降对所有索引更多的控制所有的表，所以我写了下面的存储过程做一个表格进行查询：
CREATE PROCEDURE [dbo].[sp_DropAllIndexesOnTable]
@TableName varchar(1000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DropCommand1 nvarchar(4000)
DECLARE @DropCommand2 nvarchar(4000)
--Create Temp Table to hold the names and table names of all Clustered Indexes
SELECT o.name AS TableName, i.name AS IndexName
INTO #AllClustered
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id=o.object_id
WHERE o.type <> 'S'
AND is_primary_key = 1
--Create Temp Table to hold the names and table names of all NonClustered Indexes
SELECT o.name AS TableName, i.name AS IndexName
INTO #AllNonClustered
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id=o.object_id
WHERE o.type <> 'S'
AND is_primary_key <> 1
AND index_id > 0
--Create DROP CONSTRAINT command for the Primary Key Constraint if there is one
SELECT @DropCommand1 = ( SELECT 'ALTER TABLE dbo.['+ TableName +'] DROP CONSTRAINT ['+ IndexName +'];'
FROM #AllClustered
WHERE TableName = @TableName
FOR xml path('') );
--Create DROP INDEX command for the indexes on the table if there are any
SELECT @DropCommand2 = ( SELECT 'DROP INDEX [' + IndexName + '] ON dbo.['+ TableName +'];'
FROM #AllNonClustered
WHERE TableName = @TableName
FOR xml path('') );
IF (@DropCommand1 IS NULL AND @DropCommand2 IS NULL) PRINT 'No action taken, zero indexes found on table ' + @TableName
IF @DropCommand1 IS NOT NULL EXEC sp_executesql @DropCommand1
IF @DropCommand2 IS NOT NULL EXEC sp_executesql @DropCommand2
DROP TABLE IF EXISTS #AllClustered
DROP TABLE IF EXISTS #AllNonClustered
END
GO
I cycle through the specific tables in my DB which I want to drop indexes on using a loop, and I drop the indexes by calling this proc with the table name, and recreate better ones right after. This way, only one table at a time has no indexes.
我循环遍历数据库中的特定表，我想使用循环删除索引，然后通过使用表名调用此过程来删除索引，然后立即重新创建更好的索引。这样，一次只有一张表没有索引。
The reason I do this and the number of tables I do it on would make your head spin, but I definitely needed a proc like this!
我这样做的原因以及我这样做的桌子数量会让您头晕目眩，但我绝对需要这样的过程！
回答by John
Minor improvements to the accepted answer that I had to make in my own case, mostly to account for schemas:
我必须在我自己的情况下对已接受的答案进行小幅改进，主要是为了说明模式：
declare @qry nvarchar(4000);
select @qry = (select 'drop index ['+s.name+'].['+o.name+'].['+i.name+'];'
from sys.indexes i join sys.objects o on i.object_id=o.object_id join sys.schemas s on o.schema_id=s.schema_id
where o.type<>'S' and is_primary_key<>1 and index_id>0 and s.name<>'sys'
for xml path(''));
exec sp_executesql @qry
Also: In my case it couldn't run in one go because the script becomes longer than 4000 characters. The only way I could think of to deal with that was to put a "top 20" on the inner select and execute it multiple times.
另外：就我而言，它无法一次性运行，因为脚本长度超过 4000 个字符。我能想到的唯一方法是在内部选择上放置一个“前 20 个”并多次执行它。
回答by Felipe Deveza
SELECT 'DROP INDEX [' + IX.NAME + '] ON ' + OBJECT_NAME(IX.OBJECT_ID) + '; '
FROM SYS.INDEXES IX
JOIN SYS.OBJECTS O ON IX.OBJECT_ID = O.OBJECT_ID
INNER JOIN SYS.SCHEMAS S ON O.SCHEMA_ID = S.SCHEMA_ID
WHERE
IX.NAME IS NOT NULL
AND O.TYPE <> 'S'
AND IS_PRIMARY_KEY <> 1
AND INDEX_ID > 0
AND S.NAME != 'SYS' AND S.NAME!= 'SYS' AND IS_UNIQUE_CONSTRAINT = 0
Modify conditions according to your needs
根据您的需要修改条件
If u want to delete PK constraints, you will get this message if you try to drop index:
如果您想删除 PK 约束，如果您尝试删除索引，您将收到此消息：
An explicit DROP INDEX is not allowed on index... It is being used for PRIMARY KEY constraint enforcement.
索引上不允许显式 DROP INDEX...它用于 PRIMARY KEY 约束强制执行。
Then, use this...
那么，用这个...
SELECT 'ALTER TABLE [' + O.NAME + '] DROP CONSTRAINT ' + IX.NAME + '; '
FROM SYS.INDEXES IX
JOIN SYS.OBJECTS O ON IX.OBJECT_ID = O.OBJECT_ID
INNER JOIN SYS.SCHEMAS S ON O.SCHEMA_ID = S.SCHEMA_ID
WHERE
IX.NAME IS NOT NULL
AND O.TYPE <> 'S'
AND INDEX_ID > 0
AND S.NAME != 'SYS' AND S.NAME!= 'SYS'
