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

24 comments:

  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

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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. This is very interesting, but it is necessary to click on this link: cheap dedicated server germany

    ReplyDelete
  7. Importance – Some specialists guarantee that the associations occupied with information mining don't have to concentrate on DA. Data Analytics Course

    ReplyDelete
  8. They will be able to run their mind over many different points of view and to come to their own conclusion. If they only had access to one point of view, it would be a lot harder for them to know whether or not it is accurate.
    Bobby White

    ReplyDelete
  9. The articles can contain general data about points your clients may be keen on. On the off chance that you do it compose with earnest enthusiasm for your clients prosperity this achievement can be accomplished agreeable to you. blog comments service in 1$

    ReplyDelete
  10. You the Affiliate Marketers will discover handpicked tips, instruments and assets for your utilization SEO PACKAGE

    ReplyDelete
  11. you can contract a Ghost author or a Company who manages web crawler arranged catchphrase arrive at content and write to expandBacklinks service

    ReplyDelete
  12. I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation. Thanks a lot for keeping great stuff. I am very much thankful for this site. hotmail-inicio-sesion.com/

    ReplyDelete
  13. Thank you for your post.Really looking forward to read more. Cool.satta king

    ReplyDelete
  14. At once the blog might possibly unquestionably pick up famed very from post the general public, as for the meticulous content articles or maybe just necessary review articlesSatta king

    ReplyDelete
  15. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
    data analytics course

    ReplyDelete
  16. The triumphant numbers are settled on an arbitrary premise just as hence, numbers picked by you haphazardly may end up having far superior odds of winning than any arrangement of carefully arranged just as planned numbers.satta king

    ReplyDelete
  17. This is exactly the information I'm looking for, I couldn't have asked for a simpler read with great tips like this... Thanks! ExcelR Data Analytics Courses In Pune

    ReplyDelete
  18. # 1 is often the capacity along with the technical specs in the servers for you to shall be using ark server hosting

    ReplyDelete