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

40 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
  19. It is not a question anymore of "Do I need SEO?" Most companies have seen their competitors gain leads, more customers, more sales, etc. from SEO services. The present question companies are asking themselves, is "Should I outsource SEO?" This article outlines the benefits of outsourcing SEO services, as opposed to hiring an SEO Specialist in-house.
    מסה מדיה קידום בגוגל

    ReplyDelete
  20. Know more about Data Analytics
    Cool stuff you have, and you keep overhaul every one of us.

    ReplyDelete
  21. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. keep it up.
    data analytics course in Bangalore

    ReplyDelete
  22. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. keep it up.
    data analytics course in Bangalore

    ReplyDelete
  23. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data Science Course

    ReplyDelete
  24. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time sql dba training

    ReplyDelete
  25. Thank you, it helped me a lot.

    ReplyDelete
  26. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
    Data Science Course

    ReplyDelete
  27. You guardians do an astounding web diary, and have some unfathomable substance. Continue doing extraordinary. https://serverbrowse.com/

    ReplyDelete
  28. Your post is very helpful to get some effective tips to reduce weight properly. You have shared various nice photos of the same. I would like to thank you for sharing these tips. Surely I will try this at home. Keep updating more simple tips like this.  https://serverbrowse.com/

    ReplyDelete
  29. Facilitating of a solitary or a few sites. In this sort of arrangement, the entirety of the administrations/programs are introduced and running on one dedicated server. High Performance Dedicated Servers

    ReplyDelete
  30. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression

    ReplyDelete
  31. This blog is so nice to me. I will keep on coming here again and again. Visit my link as well.. drip likes instagram

    ReplyDelete
  32. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression

    ReplyDelete
  33. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
    sql server dba online training

    ReplyDelete
  34. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    data analytics courses

    ReplyDelete