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


Find-SqlDuplicateIndex

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”

Find-SqlDuplicateIndex -SqlServer sql2005 -FileName C:\temp\sql2005-DuplicateIndexes.sql

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

Find-SqlDuplicateIndex -SqlServer sqlserver2014a -SqlCredential $cred

To find exact duplicate indexes on both db1 and db2 databases

 Find-SqlDuplicateIndex -SqlServer sqlserver2014a -Databases db1, db2

To find exact duplicate or overlapping indexes on all user databases

 Find-SqlDuplicateIndex -SqlServer sqlserver2014a -IncludeOverlapping

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

From PowerShell, execute   Get-Help Find-SqlDuplicateIndex -Detailed   for more information on this function. Want to see the source code? See it on GitHub