How to know if a maintenance plan exists, editing a maintenance plan, deleting maintenance plan?

Problem: How to know if a maintenance plan exists, editing a maintenance plan, deleting maintenance plan?

Solution:

Maintenance plans information is stored in msdb database and it has few tables and system catalog views where we can query the necessary information for troubleshooting or finding what you are looking for.

Below is the list of views which specifies type of data it has.

Object Name	Information Stored
sysmaintplan_plans	list of maitenance plans on a server.
sysmaintplan_subplans	maintenance plans and sub plans in the plan.
sysmaintplan_log	log of a subplan or maintance plans.
sysmaintplan_logdetail	logging detail of subplan and maintenance plans.

To Edit or Delete a Maintenance plan, you must click through a few buttons and below are the screenshots to help you on where to look for.

Step 1: In the below picture i am modifying an existing plan by right clicking the maintenance plan. 

Step 2: In the below picture i am modifying an existing sub plan by right clicking the sub plan to get edit options in the design window. 

Example 1:

In this example, I would like to show how to retrieve error information from a maintenance plan and the commands and the error message and other details which will be useful for trouble shooting.

-- Consolidated Maintenance plan Information.

SELECT p.NAME AS Maintenanceplan,

                sp.subplan_name AS MaintenanceSubPlan,

                plog.start_time,

                plog.end_time,

                plog.succeeded,

                plogd.line1 as TaskName,

                plogd.line2 as ConnectionsUsed,

                plogd.line3 as Databases_Selected,

                plogd.line4 as TypeofSubPlan,

                plogd.line5 as Optional,

                plogd.error_number,

                plogd.error_message,

                plogd.command

FROM msdb.dbo.sysmaintplan_plans p

INNER JOIN msdb.dbo.sysmaintplan_subplans sp

ON p.id = sp.plan_id

INNER JOIN msdb.dbo.sysmaintplan_log plog

                ON plog.plan_id = sp.plan_id

                                AND plog.subplan_id = sp.subplan_id

INNER JOIN msdb.dbo.sysmaintplan_logdetail plogd

                ON plog.task_detail_id = plogd.task_detail_id

                Where plog.succeeded=0

OUTPUT:

Maintenanceplan	TaskName	error_message	command
DB MaintenancePlan	Back Up Database (Transaction Log) (TROPPLTMSAMBU)	Executing the query "BACKUP LOG [Test] TO  DISK = N'C:\\Transactional Ba..." failed with the following error:	BACKUP LOG [Test] TO  DISK = N'' GO