There could be scenarios when we want to search SQL Server Agent Jobs
- Find SQL Server Agent Job if it is using some specific Stored procedure in Job Step Command
- Search the SQL Server Agent Job Steps for specific script "Select Count(*) from dbo.Table"
- Search for Comments in the queries we have used in SQL Server Agent jobs Steps
We will be using two system tables to search for the string if it is used in SQL Server Agent Job. SysJobs and SysJobSteps are two system tables which exists in MSDB Database. we can use both of them and get the final results for our requirements. In this example we are searching for string if any job is using "Select count(*) from dbo.Test".
SELECT Job.name AS JobName,
Job.enabled AS ActiveStatus,
JobStep.step_name AS JobStepName,
JobStep.command AS JobCommand
FROM sysjobs Job
INNER JOIN sysjobsteps JobStep
ON Job.job_id = JobStep.job_id
WHERE JobStep.command LIKE '%Select count(*) from dbo.Test%' --You can change here what you are searching for