List the name of the duplicate index when checking for same

[MrBlueSky]

Feature Request

The duplicate index check doesn't list the indices that were identified as duplicates. Limits the usefulness of the check since you need a separate tool or query to find out the actual index names.

[MikeyBronowski]

@MrBlueSky not sure what version do you have, but the latest gives this error message:
Executing script C:\Users\micha\OneDrive\GIT\dbachecks\checks\Database.Tests.ps1

  Describing Duplicate Index

    Context Testing duplicate indexes on localhost,1433
      [-] Database Mikey should return 0 duplicate indexes on localhost,1433 1.22s
        Expected 0, because Duplicate indexes waste disk space and cost you extra IO, CPU, and Memory, but got 4.
        61:     @(Find-DbaDbDuplicateIndex -SqlInstance $Instance -Database $Database).Count | Should -Be 0 -Because "Duplicate indexes waste disk space and cost you extra IO, CPU, and Memory"
        at Assert-DatabaseDuplicateIndex, C:\Users\micha\OneDrive\GIT\dbachecks\internal\assertions\Database.Assertions.ps1: line 61
        at <ScriptBlock>, C:\Users\micha\OneDrive\GIT\dbachecks\checks\Database.Tests.ps1: line 308

As you may notice there is this bit
Find-DbaDbDuplicateIndex -SqlInstance $Instance -Database $Database

If I adjust it to my environment like that for example:
Find-DbaDbDuplicateIndex -SqlInstance $sql -Database Mikey | FT

i am going to get all four duplicates mentioned above:

DatabaseName TableName  IndexName           KeyColumns                   IncludedColumns IndexType    IndexSizeMB CompressionDescription RowCount IsDisabled
------------ ---------  ---------           ----------                   --------------- ---------    ----------- ---------------------- -------- ----------
Mikey        dbo.Table1 CI_col1             col1 ASC                                     CLUSTERED       0.000000 NONE                          0      False
Mikey        dbo.Table1 NI_col1             col1 ASC                                     NONCLUSTERED    0.000000 NONE                          0      False
Mikey        dbo.Table1 NI_col1_col2_col3   col1 ASC, col2 ASC, col3 ASC                 NONCLUSTERED    0.000000 NONE                          0      False
Mikey        dbo.Table1 NI_col1_col2_col3_2 col1 ASC, col2 ASC, col3 ASC                 NONCLUSTERED    0.000000 NONE                          0      False

Find-DbaDbDuplicateIndex is part of the dbatools anyway, so no need to get additional tools (see https://docs.dbatools.io/#Find-DbaDbDuplicateIndex).

Would it work for you?

[SQLDBAWithABeard]

This is for checking the existence of duplicate checks - attempting to parse them would be troublesome. We could add a suggestion to the because clause