How to query SQL 2005 Maintenance Job Information
Posted on 2009-02-16
I am trying to figure out how to query the Maintenance Plan Jobs and their associated detail (sub plans, schedule detail, etc.). I read a previous article called "SQL query to show all databases in a Maint Plan" however the entire script is for SQL 2000.
I know I can get the list of the plan names from:
SELECT * FROM sysmaintplan_plans (this is a view)
I can get schedule info from:
SELECT * FROM [MSDB].[dbo].[sysschedules]
And there are other tables such as "msdb.dbo.sysmaintplan_subplans"
So what I want is to be able to query or script so I can obtain a list of all the maintenance jobs on a SQL server with the schedule details for each.