Select distinct p.[name] as 'Maintenance_Plan'
, RIGHT(smpld.line4,LEN(smpld.line4)-6) as 'Backup_Type'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, job.[name] As 'Job_Name'
From msdb.dbo.sysjobs As job
join msdb.dbo.sysmaintplan_subplans sp
On sp.job_id = job.job_id
inner join msdb.dbo.[sysmaintplan_plans] p
On p.id = sp.plan_id
join msdb.dbo.sysmaintplan_log smpl
On p.id = smpl.plan_id
and sp.subplan_id =smpl.subplan_id
join msdb.dbo.sysmaintplan_logdetail smpld
On smpl.task_detail_id=smpld.task_detail_id
Where
job.[enabled] = 1
and smpld.line3 <> ''