Get-DbaHelpIndex - Bad joins based on Index Name instead of Index ID when Indexes in same DB named the same

[Sirwill1968]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Not an error just bad data. I discovered when running the module on a database that 2 different tables had the indexes named exactly the same. This caused the key and included columns to be exactly the same even though they were vastly different on the include columns.

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference

Get-DbaHelpIndex -SqlInstance rs-la-sql-01 -Database Test2

Please confirm that you are running the most recent version of dbatools

2.1.23

Other details or mentions

I copied the SQL out of the module and tweaked it in SQL and found changes to fix it. The following changes will fix the module. I've only tested the SQL not the whole module, and I don't know how to make changes to the module.

From the version in Github right now these changes will fix the join from joining on the Index name to joining on the Index_ID
Attached a SQL script that creates a test2 database along with the tables and indexes I used during the test. Make the test db then run the module and see the error when you compare it to the actual indexes. I ran across this problem on a production DB where a DEV made 2 different tables have the exact same Index name.

Test2IndexIssue.zip

Line 407 change    WHERE    ci2.name = ci.name   
to                 WHERE    ci2.index_id = ci.index_id
Line 410 change    ci2.ColumnName   
to                 ci2.ColumnName,ci2.index_column_id


Line 417 change    WHERE     ci3.name = ci.name
to                 WHERE     ci3.index_id = ci.index_id
Line 420 change    ci3.ColumnName
to                 ci3.ColumnName,ci3.index_column_id
Line 448 added ci.index_id, in the group by after ci.name ,

The same changes 
Line 804 change    WHERE    ci2.name = ci.name   
to                 WHERE    ci2.index_id = ci.index_id
Line 807change    ci2.ColumnName   
to                 ci2.ColumnName,ci2.index_column_id
Line 814 change    WHERE     ci3.name = ci.name
to                 WHERE     ci3.index_id = ci.index_id
Line 817 change    ci3.ColumnName
to                 ci3.ColumnName,ci3.index_column_id
Line 845 added ci.index_id, in the group by after ci.name ,

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe), Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.14393.6343
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14393.6343
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

Name Value

---

PSVersion 7.4.2
PSEdition Core
GitCommitId 7.4.2
OS Microsoft Windows 10.0.14393
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM-CU13-GDR) (KB5040939) - 16.0.4131.2 (X64) Jun 21 2024 21:57:17 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor)

and

Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64) Mar 29 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.7.4095.0