Querying SQL Server maintenance plans

A request came in to enumerate active maintenance plans and associated jobs across a range of servers.
Here is the query I developed for the purpose.

select 
	p.name as 'Maintenance Plan'
	,p.[description] as 'Description'
	,p.[owner] as 'Plan Owner'
	,sp.subplan_name as 'Subplan Name'
	,sp.subplan_description as 'Subplan Description'
	,j.name as 'Job Name'
	,j.[description] as 'Job Description'  
from msdb..sysmaintplan_plans p
	inner join msdb..sysmaintplan_subplans sp
	on p.id = sp.plan_id
	inner join msdb..sysjobs j
	on sp.job_id = j.job_id
where j.[enabled] = 1

As you can see from the where clause – I am only interested in filtering for active jobs. I’m also only returning basic name information, I only really want to know what plans exist and what are the jobs associated with them. Other columns are available as you wish. If you really love resolving bitwise operators you might want to join to msdb..sysschedules and return schedule information 🙂