When I run my query
SELECT * FROM sys.sysobjects WHERE xtype = 'U'
I find there are 2 more tables added trace_xe_action_map and trace_xe_event_map, I realize then that these two siblings come from extended events but I remember inserting them into my database. Yet I don't know to which xtype they are both belonging to. Because if I run the above query in SQL Server Management Studio; I won't be able to find them. What query should I make to display them in SQL Server Management Studio?
trace_xe_action_map (Transact-SQL)
Contains one row for each Extended Events action that is mapped to a SQL Trace column ID. This table is stored in the master database, in the sys schema.
Applies to: SQL Server 2012+.
trace_xe_event_map (Transact-SQL)
Contains one row for each Extended Events event that is mapped to a SQL Trace event class. This table is stored in the master database, in the sys schema.
Applies to: SQL Server 2012+.
You can use the following query to identify the Extended Events events that are equivalent to the SQL Trace event classes:
SELECT te.name, xe.package_name, xe.xe_event_name
FROM sys.trace_events AS te
LEFT JOIN sys.trace_xe_event_map AS xe
ON te.trace_event_id = xe.trace_event_id
WHERE xe.trace_event_id IS NOT NULL
Not all event classes have equivalent Extended Events events. You can use the following query to list the event classes that do not have an Extended Events equivalent:
SELECT te.trace_event_id, te.name
FROM sys.trace_events AS te
LEFT JOIN sys.trace_xe_event_map AS xe
ON te.trace_event_id = xe.trace_event_id
WHERE xe.trace_event_id IS NULL
By executing this query:
SELECT *
FROM sys.all_objects -- also FROM sys.system_objects
WHERE name like 'trace_xe_%'
You will have a result like this:
name | object_id | type | type_desc
--------------------+------------+------+------------
trace_xe_action_map | -463397375 | U | USER_TABLE
trace_xe_event_map | -319884821 | U | USER_TABLE
