Get-DbaSchemaChangeHistory - Wrong Object Type with Indexes

[Sirwill1968]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Not an error but wrong data. I noticed that for a schema change involving an Index creation it shows as a User_Table as object type. After much looking at things I discovered that the sys.objects table stores that type for the object ID. But the trace file itself lists it as a type of IX which is Index. See https://learn.microsoft.com/en-us/sql/relational-databases/event-classes/objecttype-trace-event-column?view=sql-server-ver16
Sadly the table in that article is not available in a SQL Table. However for an Index Object type on the trace file indextype column it is 22601. In this query you can see that for a event id of 46 and the subclas value from that query returns IX

select * from sys.trace_subclass_values where subclass_value=22601 and trace_event_id=46
This query returns 94 different object type codes for the Create event 46
select distinct trace_event_id,Subclass_name,Subclass_value from sys.trace_subclass_values where trace_column_id=28 and trace_event_id=46
If you were to select these columns from the query in this powershell you would get the true object name and type. You could do a big case statement with the data from the true object type list and properly give the correct information.
,tt.ObjectName,tt.ObjectType

Steps to Reproduce

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

Get-DbaSchemaChangeHistory -SqlInstance trsqlag -Since (Get-Date).AddDays(-1)-ExcludeDatabase master,MSDB,TEMPDB |Write-DbaDbTableData -SqlInstance trsqlag -Database bb -Table SchemaChanges

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

2.1.23

Other details or mentions

Spent a lot of time researching the default trace, the query, and what fields where what, and what was stored in SQL and what was not.

What PowerShell host was used when producing this error

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

PowerShell Host Version

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 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.7.4095.0