Toggle Jobs On and Off

This is a useful script if you want to turn a specific list of jobs that are currently enable to a disabled status and vice versa.

Just specify the list of job names in the VALUES section below:

[cc lang=”sql”]
use msdb
go

IF OBJECT_ID(N’tempdb..#jobs’) IS NOT NULL
BEGIN
DROP TABLE #jobs
END

CREATE TABLE #jobs
(
job_name nvarchar(max),
)
INSERT INTO #jobs(job_name)
VALUES
(‘Job Name 1 here’),
(‘Job Name 2 here’),
(‘Run index selection Job’)

IF OBJECT_ID(N’tempdb..#job_status’) IS NOT NULL
BEGIN
DROP TABLE #job_status
END

SELECT ID = identity(int, 1,1),
[job_name], enabled
INTO #job_status
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
JOIN #jobs j
ON j.job_name = sJOB.name

DECLARE @job_name nvarchar(max) = ”;
DECLARE @enabled int;
DECLARE @iter int = (SELECT MAX(ID) FROM #job_status);

WHILE @iter > 0
BEGIN
SET @job_name = ”

SELECT @job_name = job_name,
@enabled = CASE WHEN [enabled] = 0 THEN 1 ELSE 0 END
FROM #job_status
WHERE ID = @iter;

EXEC msdb.dbo.sp_update_job @job_name = @job_name, @enabled = @enabled ;

PRINT cast(@job_name as varchar) + ‘:’ + cast(@enabled as varchar)
SET @iter -= 1;

END
[/cc]