Monday, 6 January 2014

Search content of SQL Server Agent Jobs

If you have many SQL Server Agent jobs on a server, it is useful to be able to search for a job programatically, based on it's contents, rather than manually viewing each step of each job. The following SQL allows you to search for code within all SQL jobs:
USE [msdb]
GO
SELECT     
      j.name,
      js.step_id,
      js.command,
      j.enabled,
      j.job_id
FROM  dbo.sysjobs j
JOIN  dbo.sysjobsteps js
      ON    js.job_id = j.job_id
WHERE js.command LIKE N'%Insert search string here%'
ORDER BY j.name

No comments:

Post a Comment

Updating massive amount of rows whilst avoiding blocking

The following SQL is a good means to split an update on a massive table into smaller chunks, whilst reducing blocking. The method is to upda...