How to Drop All Your Indexes – Fast

Sometimes I need to reset stuff during performance training classes. I know some of you teach classes, too, and some of you just like doing crazy stuff.

So here you go, a stored procedure to lose weight fast:

DropIndexes for SQL Server 2016 & Newer

DropIndexes for SQL Server 2008-2014

This one doesn’t include support for the @ExceptIndexNames parameter because I’m lazy:

Look, you’re the one who reads this blog, alright? Don’t blame me for the fact that you’re – STOP COPYING THAT, AND NO, DO NOT PASTE THAT INTO PRODUCTION.

No, this one’s not going into the First Responder Kit.

Previous Post
sp_AllNightLog: Creating Jobs Like I’m The President
Next Post
Abstracts Open for GroupBy December

23 Comments. Leave new

  • While I agree that this shouldn’t be in the First Responder Kit, I can see a project in the works to the tune of “Training Helper Kit” maybe.

    Reply
  • If only losing weight this fast worked this well in real life…

    Reply
  • A Refractor one,
    CREATE PROCEDURE [dbo].[DropIndexes] AS
    SET NOCOUNT ON

    DECLARE @AllIndex varchar(max),@AllStatistics varchar(max)

    SELECT @AllIndex=DBO.GROUP_CONCAT_D(‘DROP INDEX [‘ + i.name + ‘] ON [‘ + s.name + ‘].[‘ + t.name + ‘]’,’;’)
    FROM sys.tables t
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE i.type = 2

    SELECT @AllStatistics=DBO.GROUP_CONCAT_D(‘DROP STATISTICS ‘ + SCHEMA_NAME([t].[schema_id]) + ‘.’ + OBJECT_NAME([s].[object_id]) + ‘.’ + s.[name],’;’)
    FROM sys.[stats] AS [s]
    JOIN sys.[tables] AS [t]
    ON s.[object_id] = t.[object_id]
    WHERE [s].[name] LIKE ‘[_]WA[_]Sys[_]%’
    AND OBJECT_NAME([s].[object_id]) NOT LIKE ‘sys%’

    —-With out CLR
    –SELECT @AllIndex=(select
    — stuff((select ‘;’+’DROP INDEX [‘ + i.name + ‘] ON [‘ + s.name + ‘].[‘ + t.name + ‘]’
    –FROM sys.tables t
    –INNER JOIN sys.indexes i ON t.object_id = i.object_id
    –INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    –WHERE i.type = 2
    –for xml path(”), root(‘MyString’), type ).value(‘/MyString[1]’,’varchar(max)’)
    — , 1, 2, ”))

    –SELECT @AllStatistics=(select
    — stuff((select ‘;’+’DROP STATISTICS ‘ + SCHEMA_NAME([t].[schema_id]) + ‘.’ + OBJECT_NAME([s].[object_id]) + ‘.’ + s.[name]
    –FROM sys.[stats] AS [s]
    –JOIN sys.[tables] AS [t]
    –ON s.[object_id] = t.[object_id]
    –WHERE [s].[name] LIKE ‘[_]WA[_]Sys[_]%’
    –AND OBJECT_NAME([s].[object_id]) NOT LIKE ‘sys%’
    –for xml path(”), root(‘MyString’), type ).value(‘/MyString[1]’,’varchar(max)’)
    — , 1, 2, ”))

    select @AllIndex,@AllStatistics

    –EXEC (@AllIndex)

    –EXEC (@AllStatistics)
    GO

    Reply
  • I was about to try that on a nice, busy production server … but I saw the cursor just in time… 🙂

    Reply
  • We need a rocket telling Groot “Don’t press this button” DBA Reaction GIF for this.

    “Here’s a drop all indexes button. Don’t press it.” “I am Mongo!” “NOOOOOOOOOO!!!!!!”

    Reply
  • Tim Cartwright
    August 11, 2017 11:58 am

    Yeah, you know, you really should have read the whole post because the warnings… The warnings come *after* the script. 😉

    Reply
  • Craig Petrosky
    August 11, 2017 2:10 pm

    I tried this on a database I was going to drop anyway (TEST SERVER!) and got this message: Msg 3723, Level 16, State 4, Line 8
    An explicit DROP INDEX is not allowed on index ‘dbo.’. It is being used for PRIMARY KEY constraint enforcement. So it isn’t “set it and forget it” after all.

    Reply
  • No kidding, I built almost the exact same thing this week. If only this had come out Monday…

    I still wouldn’t have used the cursor, though. It’s like 2017 already…

    Reply
  • Don’t use a cursor. SELECT all the commands to the output, let the guy copy and paste into the executable window and take responsibility for himself.

    Reply
  • Here’s a thought: if you want to “reset stuff” wouldn’t RESTORE DATABASE… have done the job?

    Reply
  • would be nice to have a parameter where you can provide a table name and only the indexes of that table would be dropped

    Reply
    • Marco – hahaha, funny you mention that! I actually updated it for our Mastering Query Tuning classes and I forgot to update this blog post. It’s now updated. Enjoy!

      Reply
  • Aaron Cutshall
    January 30, 2018 9:44 am

    Brent — I’m just wondering if there’s an advantage to executing each statement individually as opposed to generating a script to do everything and then execute it. This approach also has the benefit of eliminating the cursor and could be modified to be run in a TRY-CATCH within a transaction:

    CREATE PROCEDURE [dbo].[DropIndexes]
    @SchemaName NVARCHAR(255) = NULL,
    @TableName NVARCHAR(255) = NULL
    AS BEGIN
    SET NOCOUNT ON;

    DECLARE @CurrentCommand NVARCHAR(MAX);

    SELECT @CurrentCommand = ISNULL(@CurrentCommand + NCHAR(10), N”) +
    N’DROP INDEX [‘ + i.name + N’] ON [‘ + s.name + N’].[‘ + t.name + N’];’
    FROM sys.tables t
    INNER JOIN sys.indexes i
    ON i.object_id = t.object_id
    AND i.type = 2
    INNER JOIN sys.schemas s
    ON s.schema_id = t.schema_id
    AND s.name = COALESCE(@SchemaName, s.name)
    WHERE t.name = COALESCE(@TableName, t.name);

    SELECT @CurrentCommand = ISNULL(@CurrentCommand + NCHAR(10), N”) +
    N’DROP STATISTICS ‘ + SCHEMA_NAME(t.schema_id) + N’.’ +
    OBJECT_NAME(s.object_id) + N’.’ + s.name
    FROM sys.stats s
    INNER JOIN sys.tables t
    ON t.object_id = s.object_id
    WHERE s.name LIKE ‘[_]WA[_]Sys[_]%’
    AND OBJECT_NAME(s.object_id) NOT LIKE ‘sys%’;

    EXEC(@CurrentCommand);
    END

    Reply
  • Jeffrey Mergler
    May 15, 2019 10:33 am

    I attended a SQL Saturday session by Erland Sommarskog titled “Don’t Use Cursors! Or why you should”. Based on that and reviewing this I think this is a perfectly good case for a cursor: its a utility script not intended to be run repeatedly through out the day it may be run “once in a while”. There are two improvement suggestions: 1) use CURSOR STATIC LOCAL 2) there’s an anti-pattern in the While loop where you FETCH NEXT 2x. This can be done with a single fetch next using (note the single FETCH NEXT below):
    DECLARE result_cursor CURSOR STATIC LOCAL FOR
    SELECT Command FROM #commands;
    OPEN result_cursor;
    WHILE 1=1
    BEGIN
    FETCH NEXT FROM result_cursor INTO @CurrentCommand;
    IF @@FETCH_STATUS 0
    BREAK
    PRINT @CurrentCommand;
    EXEC(@CurrentCommand);
    END;

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}