After using the query from Hannah’s answer for troubleshooting, I came back to it when thinking about documenting how a trace template was configured, since a screenshot can’t show all columns (and the window size is fixed). So, then I thought I would try to make it mimic the appearance of the UI.
Of course, the script is overkill, but here’s what I came up with (showing the “Standard” trace template):
Compared to the UI:
I couldn’t determine the order of the columns used by the UI, and I’m not sure if it’s possible to know if a column is available but unchecked.
DECLARE @tid AS INT
SELECT @tid=MAX(id) FROM sys.traces
DECLARE @ColumnID INT, @ColumnName NVARCHAR(100);
DECLARE @col AS NVARCHAR(MAX) = N''
DECLARE @blanks AS NVARCHAR(MAX) = N''
DECLARE @names AS NVARCHAR(MAX) = N''
DECLARE cursor_results CURSOR FOR
SELECT DISTINCT ei.columnid, tc.name
FROM sys.traces t
CROSS APPLY sys.fn_trace_geteventinfo(t.id) ei
LEFT JOIN sys.trace_columns tc ON ei.columnid = tc.trace_column_id
WHERE t.id = @tid
ORDER BY ei.columnid
OPEN cursor_results
FETCH NEXT FROM cursor_results into @ColumnID, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @col=@col + N', ISNULL(MAX(CASE WHEN ei.columnid=' + CAST(@ColumnID AS NVARCHAR)
+ N' THEN N''✅'' END),'''') AS [' + @ColumnName + N']'
SET @blanks=@blanks + N', '''' AS [' + @ColumnName + N']'
SET @names=@names + N', [' + @ColumnName + N']'
FETCH NEXT FROM cursor_results into @ColumnID, @ColumnName
END
CLOSE cursor_results;
DEALLOCATE cursor_results;
DECLARE @sql AS NVARCHAR(4000)
SET @sql =
N'
SELECT Decoration AS '' '', EventName AS Events' + @names + N'
FROM (
SELECT N'' ✅'' AS Decoration, tcat.name Category, N'' '' + te.name EventName' + @col + N'
FROM sys.traces t
CROSS APPLY sys.fn_trace_geteventinfo(t.id) ei
LEFT JOIN sys.trace_events te ON ei.eventid = te.trace_event_id
LEFT JOIN sys.trace_categories tcat ON te.category_id=tcat.category_id
WHERE t.id = ' + CAST(@tid AS NVARCHAR) + N'
GROUP BY tcat.name, te.name
UNION ALL
SELECT DISTINCT N''⊟'' AS Decoration, tcat.name + '' '', tcat.name' + @blanks + N'
FROM sys.traces t
CROSS APPLY sys.fn_trace_geteventinfo(t.id) ei
LEFT JOIN sys.trace_events te ON ei.eventid = te.trace_event_id
LEFT JOIN sys.trace_categories tcat ON te.category_id=tcat.category_id
WHERE t.id = ' + CAST(@tid AS NVARCHAR) + N'
) AS x
ORDER BY Category, CASE WHEN Category=EventName THEN 1 ELSE 0 END DESC';
EXEC sp_executesql @sql;
SELECT tc.name [Column], CASE WHEN f.logical_operator=0 THEN 'AND' ELSE 'OR' END AS Operator,
CASE WHEN f.comparison_operator=0 THEN '='
WHEN f.comparison_operator=1 THEN '<>'
WHEN f.comparison_operator=2 THEN '>'
WHEN f.comparison_operator=3 THEN '<'
WHEN f.comparison_operator=4 THEN '>='
WHEN f.comparison_operator=5 THEN '<='
WHEN f.comparison_operator=6 THEN 'LIKE'
WHEN f.comparison_operator=7 THEN 'NOT LIKE'
END AS Comparison, f.value
FROM fn_trace_getfilterinfo(@tid) f
LEFT JOIN sys.trace_columns tc ON f.columnid = tc.trace_column_id ;
