删除本数据库所有作业(jobs)的T-SQL语句（求更好的办法）
删除本数据库所有作业(jobs)的T-SQL语句.

我的做法
--Delect   all   jobs   in   this   server!--
use   msdb
declare   jobs_cursor   cursor  
  for  
          select   job_id   from   msdb.dbo.sysjobservers
open   jobs_cursor
declare   @t_job_id   uniqueidentifier
fetch   next   from   jobs_cursor   into   @t_job_id
WHILE   (@@fetch_status <> -1)
BEGIN
EXEC   sp_delete_job   @job_id=@t_job_id
fetch   next   from   jobs_cursor   into   @t_job_id
END
deallocate   jobs_cursor



------解决方案--------------------
好像只能向lz这样做
------解决方案--------------------
--或者

declare @sql varchar(8000)
Select @sql=isnull(@sql, ' ')+ 'EXEC msdb.dbo.sp_delete_job ' ' '+rtrim(job_id)
+ ' ' ' '+Char(13)from msdb.dbo.sysjobservers
exec(@sql)
------解决方案--------------------
sp_delete_job 是这样删除的。


-------
...
INSERT INTO #temp_jobs_to_delete
SELECT job_id, (SELECT COUNT(*)
FROM msdb.dbo.sysjobservers
WHERE (job_id = @job_id)
AND (server_id = 0))
FROM msdb.dbo.sysjobs_view
WHERE (job_id = @job_id)
.....
-- Delete all traces of the job
BEGIN TRANSACTION

DELETE FROM msdb.dbo.sysjobs
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

DELETE FROM msdb.dbo.sysjobservers
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

DELETE FROM msdb.dbo.sysjobsteps
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

DELETE FROM msdb.dbo.sysjobschedules
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

IF (@delete_history = 1)
DELETE FROM msdb.dbo.sysjobhistory
WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

COMMIT TRANSACTION
------解决方案--------------------
学习 接分
------解决方案--------------------
不懂 友情UP