How can I drop all indexes in a SQL database with one command?
Solution 1
You're very close.
declare @qry nvarchar(max);
select @qry =
(SELECT 'DROP INDEX ' + quotename(ix.name) + ' ON ' + quotename(object_schema_name(object_id)) + '.' + quotename(OBJECT_NAME(object_id)) + '; '
FROM sys.indexes ix
WHERE ix.Name IS NOT null and ix.Name like '%prefix_%'
for xml path(''));
exec sp_executesql @qry
Solution 2
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
Solution 3
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
Solution 4
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);
Solution 5
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.
Also, I wanted more control over the process than dropping all indexes on all tables, 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!
![djangofan](https://i.stack.imgur.com/f2tOS.jpg?s=256&g=1)
djangofan
I always pay it forward. I ask questions so I can learn and I try to help others.
Updated on July 05, 2022Comments
-
djangofan about 2 years
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"?
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.
-----------------------
With Rob Farleys help, final draft of the script is:
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