SQL Server Agent: Disable and enable all jobs

CREATE PROC prc_DBA_job_Enable_Disable @penable bit
AS
BEGIN
/****HeaderStart

Author: Kevin Foreman
Date: 10-04-2007
Description:
Enable/disable all jobs on a server based on current enable status
Places all enabled job names into a table in DBAFunctions and loops
through to disable them. Loops through to enable again and then clears table.

Designed to be used twice as a wrapper around a process/task that reqiures
jobs disabled.

Starts auto-start jobs when re-enabling them.

-- CREATE TABLE [tbl_DBA_job_Enable_Disable] (
-- [job_id] [uniqueidentifier] NULL ,
-- [name] [sysname] NOT NULL ,
-- [job_enabled] [tinyint] NULL ,
-- [freq_type] [int] NULL ,
-- [schedule_enabled] [tinyint] NULL ,
-- [last_updated] [datetime] NULL ,
-- [updated_by] [sysname] NOT NULL
-- )


Input: @penable 1 = enable jobs / 0 = disable

Example:
EXEC DBAFunctions.dbo.prc_DBA_job_Enable_Disable @penable = 0
*** [do something] ***
EXEC DBAFunctions.dbo.prc_DBA_job_Enable_Disable @penable = 1

Health Warning: NO ERROR TRAPPING

Version: SQL2000.01.00P
Maintained:


HeaderEnd****/
SET NOCOUNT ON

DECLARE @command TABLE (Command nvarchar(1000) , Done bit)
DECLARE @sql nvarchar(1000)
DECLARE @s nvarchar(1000)

--cleardown and repopulate the holding table if we are disabling jobs
IF @penable = 0
BEGIN

SELECT @s = 'DISABLING jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s
DELETE [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]

--get the name and id of enabled jobs
INSERT [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
(job_id,name,job_enabled,freq_type,schedule_enabled,last_updated,updated_by)
SELECT SJ.job_id,SJ.name, SJ.enabled,SJS.freq_type,SJS.enabled, GETDATE(), SYSTEM_USER
FROM msdb.dbo.sysjobs AS SJ
LEFT OUTER JOIN msdb.dbo.sysjobschedules AS SJS
ON SJ.job_id = SJS.job_id
WHERE SJ.enabled = 1

SELECT DISTINCT name FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]

INSERT @command
SELECT DISTINCT 'EXEC msdb.dbo.sp_update_job @job_name = ' + QUOTENAME(name) + ' , @enabled = 0' AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
END

ELSE

--enable jobs
BEGIN
SELECT @s = 'ENABLING jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s
SELECT name FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]

INSERT @command
SELECT 'EXEC msdb.dbo.sp_update_job @job_name = ' + QUOTENAME(name) + ' , @enabled = 1' AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]

END

WHILE EXISTS (SELECT Command FROM @command WHERE Done = 0)
BEGIN
SELECT @sql = (SELECT TOP 1 Command FROM @command WHERE Done = 0)
SELECT @sql
--EXEC @sql
UPDATE @command SET Done = 1 WHERE Command = @sql
END

IF @penable = 1
BEGIN
--restart jobs that auto-start with SQL Server Agent
IF EXISTS (SELECT * FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
WHERE freq_type = 64 AND schedule_enabled = 1)
BEGIN
SELECT @s = 'STARTING auto-start jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s

DELETE @command
INSERT @command
SELECT DISTINCT 'EXEC msdb.dbo.sp_start_job @job_name = ' + QUOTENAME(name) AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
WHERE freq_type = 64
AND schedule_enabled = 1

WHILE EXISTS (SELECT Command FROM @command WHERE Done = 0)
BEGIN
SELECT @sql = (SELECT TOP 1 Command FROM @command WHERE Done = 0)
SELECT @sql
--EXEC @sql
WAITFOR DELAY '000:00:05'
UPDATE @command SET Done = 1 WHERE Command = @sql
END
END

--cleardown table
DELETE [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
END

END