DDL Trigger Event Groups

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