USE [msdb]

declare @job_name varchar(100)

set @job_name = N'TestDrop' --作业名称

 

--删除在计划里面的日志

DELETE sysmaintplan_log

FROM sysmaintplan_subplans AS subplans INNER JOIN

sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id INNER JOIN

sysmaintplan_log ON subplans.subplan_id = sysmaintplan_log.subplan_id

WHERE (syjobs.name = @job_name)

 

--删除代理的作业

DELETE sysjobschedules

FROM sysjobs_view v INNER JOIN sysjobschedules o ON v.job_id=o.job_id WHERE v.name=@job_name

 

--删除子计划

DELETE sysmaintplan_subplans

FROM sysmaintplan_subplans AS subplans INNER JOIN

sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id

WHERE (syjobs.name = @job_name)

 

--删除作业

DELETE FROM msdb.dbo.sysjobs_view WHERE NAME = @job_name