Disable All the SQL Jobs at Once

1
2--generate disable
3SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 0' FROM msdb..sysjobs
4
5--generate enable
6SELECT 'exec msdb..sp_update_job @job_name = '''+NAME+''', @enabled = 1' FROM msdb..sysjobs

 1
 2-------------------------------------
 3-- Script to enable or disable Jobs
 4-- Filter by Category
 5-------------------------------------
 6DECLARE @Categoryname AS NVARCHAR(100) = null;
 7DECLARE @newJobStatus AS NVARCHAR(1) = '0';
 8
 9SET NOCOUNT ON
10
11DECLARE @JobName AS NVARCHAR(255);
12DECLARE @sqlEnable AS NVARCHAR(MAX);
13DECLARE @sql AS NVARCHAR(MAX);
14DECLARE @isEnabled AS NVARCHAR(1);
15
16SET @sql = 'EXEC msdb.dbo.sp_update_job @job_name = N''${JobName}'', @enabled = ' + @newJobStatus + ';'
17BEGIN TRY drop table #jobs; END TRY BEGIN CATCH /* SELECT ERROR_NUMBER() AS ErrorNumber , ERROR_SEVERITY() AS ErrorSeverity , ERROR_STATE() AS ErrorState , ERROR_PROCEDURE() AS ErrorProcedure , ERROR_LINE() AS ErrorLine , ERROR_MESSAGE() AS ErrorMessage;*/ END CATCH;
18SELECT replace(@sql, '${JobName}', sysjobs.NAME) AS sqlEnable
19,      sysjobs.NAME                              AS JobName
20,      job_id
21,      [enabled]                                    isEnabled
22INTO #jobs
23-- SELECT sysjobs.name 'Job Name' , job_id, [enabled] isEnabled, syscategories.name
24FROM       msdb.dbo.sysjobs
25INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
26WHERE
27    (syscategories.NAME = @Categoryname OR @Categoryname IS NULL) AND NOT ([enabled] = @newJobStatus)
28
29WHILE ( EXISTS ( SELECT JobName    FROM #jobs ) )
30BEGIN
31
32    SELECT TOP 1 @JobName = JobName
33    ,            @sqlEnable = sqlEnable
34    ,            @isEnabled = isEnabled
35    FROM #jobs;
36
37    DELETE FROM #jobs
38    WHERE
39        JobName = @JobName;
40
41    print @sqlEnable;
42    EXEC (@sqlEnable)
43
44END