Tag Archives: based on

How to disable all enabled jobs in SQL Server with T-Sql where the job name contains specific keyword text

Database Administrator, dba, Development, How To, SQL Server, System, T-SQL, tsql, , , , , , , , , , , , , , ,

Running the below script will output the T-Sql required to disable all enabled jobs on the SQL Server instance where the job name contains a specific keyword. Just copy the outputted text and paste into a new SSMS window and execute to disable the jobs.

For example, if you change the text value ‘PLACEHOLDER’, for the variable @KeyWord, to ‘Backup’ then any job which has ‘Backup’ in its name will have T-Sql generated to disable it. You could change the PRINT to EXEC but I wouldn’t recommend it.

DECLARE @KeyWord AS VARCHAR(256)

SET @KeyWord = 'PLACEHOLDER'
SET NOCOUNT ON

DECLARE @Job TABLE (JobName SYSNAME)
DECLARE @JobName AS SYSNAME
DECLARE @Sql AS VARCHAR(MAX)

SET @JobName = ''

INSERT INTO @Job (JobName)
SELECT j.NAME
FROM msdb.dbo.sysjobs AS j
WHERE j.[enabled] = 1
	AND j.NAME LIKE '%' + @KeyWord + '%' 
ORDER BY j.NAME ASC

WHILE @JobName IS NOT NULL
BEGIN
	SET @JobName = (
			SELECT MIN(JobName)
			FROM @Job
			WHERE JobName > @JobName
			)
	SET @Sql = '
EXEC msdb.dbo.sp_update_job @job_name = ' + '''' + @JobName + '''' + '
	,@enabled = 0; 
'

	PRINT @Sql
END