dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Find-DbaDuplicateIndex

This command will help you find duplicate and overlapping indexes in one or more databases. It will also tell you how much space you can save by dropping the index.

The command shows additional information such as the the type of compression so you can make an informed decision. Only CLUSTERED and NONCLUSTERED indexes are supported at this time.

You can select the indexes you want to drop in the grid and by click OK the drop statement will be generated.

Output that you can view on grid:

  • TableName
  • IndexName
  • KeyCols
  • IncludedCols
  • IndexSizeMB
  • IndexType
  • CompressionDesc (When 2008+)
  • NumberRows
  • IsDisabled
  • IsFiltered (When 2008+)

If you want to DROP the selected indexes right away you can use -Force parameter. Be carefull.
This way after selecting the indexes and click ok the DROP statement(s) will be executed.

Screenshots

Show duplicate indexes
Select the indexes you would like to drop from the gridview, and click OK.
Script output from selected rows
Script output from selected rows

Examples

To export SQL for the duplicate indexes in server “sql2005” chosen on grid-view and writes them to the file “C:\temp\sql2005-DuplicateIndexes.sql”

 

To find exact duplicate indexes on all user databases present on sqlserver2014a, using SQL credentials to log in.

 

To find exact duplicate indexes on both db1 and db2 databases

 

To find exact duplicate or overlapping indexes on all user databases

 

This command was created by Cláudio Silva. You can find Cláudio on Twitter and LinkedIn.

Get-Help

From PowerShell, execute Get-Help Find-DbaDuplicateIndex -Detailed for more information on this function.

Source Code

Want to see the source code? View Find-DbaDuplicateIndex.ps1 on GitHub
 

Related commands