Delete SQL Maintenance Plans

maintplan_del

DECLARE @MaintPlanFolderGUID    UNIQUEIDENTIFIER;
DECLARE @PackageName            SYSNAME;

SELECT
   @MaintPlanFolderGUID = folder.folderid
FROM
    dbo.sysssispackagefolders AS folder
    LEFT OUTER JOIN dbo.sysssispackagefolders AS parent
        ON folder.parentfolderid = parent.folderid
WHERE
    parent.folderid = '00000000-0000-0000-0000-000000000000'
AND folder.foldername = 'Maintenance Plans';

/* Get the maint plan SSIS packages */
DECLARE PackageDel CURSOR FOR
SELECT name
FROM
    dbo.sysssispackages
WHERE
    folderid = @MaintPlanFolderGUID
AND packagetype = 6;

/* Delete all the packages */
OPEN PackageDel;
FETCH NEXT FROM PackageDel INTO @PackageName;

WHILE (@@FETCH_STATUS = 0)
BEGIN;

    EXEC dbo.sp_ssis_deletepackage @name = @PackageName, @folderid = @MaintPlanFolderGUID

    FETCH NEXT FROM PackageDel INTO @PackageName;
END;

CLOSE PackageDel;
DEALLOCATE PackageDel;
GO

/* Delete the maint sub plans */
DECLARE @PlanID                 UNIQUEIDENTIFIER;

DECLARE SubPlanDel CURSOR FOR
SELECT
    subplan_id
FROM
    dbo.sysmaintplan_subplans;

OPEN SubPlanDel;
FETCH NEXT FROM SubPlanDel INTO @PlanID;

WHILE (@@FETCH_STATUS = 0)
BEGIN;

    EXEC sp_maintplan_delete_subplan @subplan_id = @PlanID, @delete_jobs = 1

    FETCH NEXT FROM SubPlanDel INTO @PlanID;
END;

CLOSE SubPlanDel;
DEALLOCATE SubPlanDel;