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

[potatoqualitee]

Thank you for your report @Sirwill1968

@claude please investigate and fix

[claude[bot]]

Claude finished @potatoqualitee's task —— View job • claude/issue-9454-20251031-1242 • Create PR ➔

---

Get-DbaSchemaChangeHistory object type issue - Fixed!

Task List:

• [x] Locate and read the Get-DbaSchemaChangeHistory function
• [x] Analyze current implementation and identify the issue
• [x] Fix the query to use trace file's ObjectType column
• [x] Check for tests and update if needed (no updates needed)
• [x] Commit and push changes
• [x] Provide PR creation link

Analysis

Found the issue in /home/runner/work/dbatools/dbatools/public/Get-DbaSchemaChangeHistory.ps1 at lines 126-127. The query was using o.type_desc from sys.objects, which returns incorrect types for indexes.

Changes Made

1. Removed sys.objects join: The query no longer joins with sys.objects and sys.schemas since the trace file contains the object information directly
2. Using trace file's ObjectName: Now using tt.ObjectName directly from the trace data
3. Added proper ObjectType mapping: Joined with sys.trace_subclass_values to map the numeric ObjectType codes (like 22601 for indexes) to their friendly names (like "IX")
4. Updated object name filter: Changed from o.name to tt.ObjectName in the WHERE clause

This ensures indexes now show as "IX" instead of "User_Table", and all other object types are correctly identified from the trace data.

Summary

The fix has been implemented and pushed to the claude/issue-9454-20251031-1242 branch. The query now correctly retrieves object type information directly from the SQL Server trace file and maps the numeric codes to their friendly names using sys.trace_subclass_values, exactly as suggested in the issue report.

---

| Branch: claude/issue-9454-20251031-1242