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


 SELECT          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


  1. This is a great post and I read a way while back. It really helped me out.Thank you so much for this great article.
    SEO Tool
    wordpress theme

  2. Thank you for excellent article.I enjoyed reading your blog!!

    Please refer below if you are looking for best Civil and Mechanical courses in Coimbatore

    AutoCAD training in Coimbatore | ArchiCAD training in Coimbatore|AutoCAD RCC Detailing training in Coimbatore|Ansys Workbench training in Coimbatore

    Keep the good work and write more like this..

  3. I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here. Text marketing automation tool

  4. Most of the blogs pretend themselves as most usable and updated blogs with new information but sometime truth might different. I want to share some facts related to this subject which will help people to enhance their skills.
    Find job on experts24hr

  5. This comment has been removed by the author.