Sometimes we need to delete all the JOBS running in the SQL Server. Given below is the script that would help to delete all the Jobs running in the SQL Server.
--USE DataBaseName;
DECLARE @Index INT
, @Count INT
, @JobName VARCHAR(255)
, @SQL NVARCHAR(4000)
DECLARE @Jobs TABLE(ID INT IDENTITY, JobName VARCHAR(255))
INSERT INTO @Jobs(JobName)
SELECT Name FROM dbo.sysjobs_view
SELECT @Index = 1
, @Count = COUNT(JobName)
FROM @Jobs
BEGIN TRY
WHILE (@Index <= @Count)
BEGIN
SELECT @JobName = JobName
FROM @Jobs
WHERE ID = @Index
SET @SQL = 'EXEC sp_delete_job @job_name = '''
+ @JobName + ''''
EXECUTE SP_EXECUTESQL @SQL
SET @Index = @Index + 1
END
SELECT 'All the Jobs are deleted successfully'
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS [Error Message]
END CATCH
The above script gets the list of all the JOBS from the database and using the SP_DELETEJOB it would delete all the JOBS one by one. Finally, it deletes all the Jobs and gives you a message that it has deleted all the JOBS.