DDL Trigger Event Groups
- Posted in:
- SQL Server
- Database Administration
I'm a big fan of DDL triggers for SQL Server. Well, except for parsing the EVENTDATA() XML. But I digress. In a post from long ago, I presented a query from Jonathan Kehayias that lists DDL trigger events and event groups. Microsoft also has a good query in their documentation for DDL Event Groups. Both are good "bottom up" queries showing all the event groups and individual events. But sometimes I want a more "compact" result set to look through that focuses on just the groups and their hierarchies...and not so much the individual events.
Hierarchies are difficult to represent in a tabular result set, of course. I aped the approach Pinal Dave uses in his SQL Server Blocking Tree article and augmented it using the STRING_AGG function and CSV lists of event groups & event names. (Note: the STRING_AGG function requires SQL Server 2017 or later.) I suspect I'll get some mileage out of it here and there. Maybe you will too.
;WITH DdlGroupTypes AS ( SELECT et.*, CAST('00000' AS VARCHAR(MAX)) AS hierarchy, CAST(1 AS INT) AS hLevel FROM sys.trigger_event_types et WHERE et.type = 10001 UNION ALL SELECT c.*, CONCAT(hierarchy, '.', c.parent_type, c.type) AS hierarchy, et.hLevel + 1 FROM sys.trigger_event_types c JOIN DdlGroupTypes et ON et.type = c.parent_type WHERE c.type_name LIKE 'DDL%' ) SELECT CASE WHEN t.hLevel = 1 THEN '' WHEN t.hLevel = 2 THEN '|----' WHEN t.hLevel = 3 THEN ' |----' WHEN t.hLevel = 4 THEN ' |----' END + t.type_name DDL_group_type, (SELECT STRING_AGG(c.type_name, ',') WITHIN GROUP(ORDER BY c.type_name) FROM DdlGroupTypes c WHERE c.parent_type = t.type) AS child_DDL_group_types, (SELECT STRING_AGG(e.type_name, ',') WITHIN GROUP(ORDER BY e.type_name) FROM sys.trigger_event_types e WHERE e.type_name NOT LIKE 'DDL%' AND e.parent_type = t.type) AS child_DDL_events FROM DdlGroupTypes t ORDER BY t.hierarchy, t.parent_type, t.type_name