How to Search Text in Steps of SQL Server Agent Jobs

We are planning to change the name of a table, we have checked the dependencies and searched in sys.sql_modules if this table is used in any other object so we can take care of it. We also want to make sure if this table is used directly in any SQL Server Agent Job.

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

and there could be more.

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".

USE msdb

GO
 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

 

Search SQL Server Agent Job Steps for Specific Text