Delete Jobs From SQL Server

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.  

Ebook Download
View all
Learn
View all