SQL Server: Disable/Remove Indexes Across Servers/Databases Using PowerShell & dbatools

This is something I had to do today. In this blog post, I am going to show you how you can disable or remove indexes matching certain name criteria or other condition(s) across multiple servers and databases in various tables. Usually, this is a very painful process if done by hand but with the fantastic work done by the dbatools team, it becomes quite easy.

The idea of the script is to

• For a given a set of SQL servers
• Scan each server and all databases within it
• In each database look at all tables
• Find indexes that match certain naming criteria or other conditions
• Export the list and the generated TSQL to disable/remove matching indexes as CSV
• Disable or remove the indexes physically

In the example below, I am looping through a bunch of servers and all databases in those servers looking for indexes with name starting with “NCIX01”. I know that I created them temporarily and would like to disable them. In your case, the criteria might be different, so alter accordingly.

Also, if you only want to view or export the results without running it, just comment out the line – “Invoke-DbaQuery” at the end.

<#
#######################
.SYNOPSIS
   Given a index name search pattern and a list of servers, finds matching indexes that can be disabled/dropped
 
.DESCRIPTION
    For the pattern of index name provided, gets the list of all matching indexes.  
    It also generates the TSQL to disable or drop the indexes. 
    Alter it as necessary to rebuild index or whatever else it is that you want to do
 
Version History
    v1.0   - Jana Sattainathan - Nov.09.2022
 
.LINK
    https://sqljana.wordpress.com
#>

[string] $indexFilter = 'NCIX01*'  #  <<<- Change this to '*' to get all indexes or to something meaningful based on your need

#List of SQL servers goes here
[string] $servers = @"
DevServerDb1
UatServerDb1
PrdServerDb1
   
DevServerb2
UatServerb2
PrdServerb2
   
DevServerDb3
UatServerDb3
PrdServerDb3
"@

[string] $disableIndexSQLTempl = 'ALTER INDEX [<<INDEX_NAME>>] ON <<SCHEMA.TABLE>> DISABLE'
[string] $dropIndexSQLTempl = 'DROP INDEX IF EXISTS [<<INDEX_NAME>>] ON <<SCHEMA.TABLE>>'

#Convert the above string into a string array that can be looped through
#----------------------------------------------------------
[string[]] $serverList = $servers.ToUpper().Split("`r").Split("`n").Split(',').Split(';') | 
                    foreach { $_.Split(".")[0]} |
                    where {$_} |
                    select -Unique

#Loop through the servers
#----------------------------------------------------------
$results = foreach ($server in $serverList){
            
    $dbs = Get-DbaDatabase -SqlInstance $server -ExcludeDatabase model,master,tempdb,msdb |
                        select name -ExpandProperty name

    #Loop through the databases on the server
    #----------------------------------------------------------
    foreach($db in $dbs)
    {
        Write-Verbose $db

        Get-DbaHelpIndex -SqlInstance $server -Database $db | 
                        Where-Object {$_.Index -like $indexFilter} |
                        Select-Object *, @{Label="DisableIndexSQL";Expression={$disableIndexSQLTempl.Replace('<<INDEX_NAME>>',$_.Index).Replace('<<SCHEMA.TABLE>>',$_.Object)}},
                                        @{Label="DropIndexSQL";Expression={$dropIndexSQLTempl.Replace('<<INDEX_NAME>>',$_.Index).Replace('<<SCHEMA.TABLE>>',$_.Object)}}
    }            
}


#Review if needed
#----------------------------------------------------------
$results | ogv

#Export the results if needed
#----------------------------------------------------------
$results | Export-Csv -LiteralPath "C:\Temp\DisableOrDropIndexes.$((get-date).ToString("yyyyMMdd_HHmmss")).csv" -NoTypeInformation

#Loop through and run the index disable TSQL if needed
#----------------------------------------------------------
foreach($idx in $results)
{
    Write-Verbose 'About to run on [$($idx.SqlInstance)].[$($idx.Database)]'
    Write-Verbose $idx.DisableIndexSQL
    Write-Verbose $idx.DropIndexSQL

    #Index will be disabled upon execution
    Invoke-DbaQuery -SqlInstance $idx.SqlInstance -Database $idx.Database -Query $idx.DisableIndexSQL
}

There is not much more to it other than the fact that you need dbatools module. You can alter it for example to only select indexes that have a certain column name in the index column list.