Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to fetch a code string from SP's that are called by SQL Agent jobs

Views: 235 views
Reading Time: 4 minutes

Hello people,

In this post I will share with you a need that I had on Friday, where a file server would be off for a few hours for maintenance and needed to identify which jobs were accessing that server and therefore, would be impacted by this maintenance, that is, search for a string in the SP's code that are called by SQL Agent jobs.

Another example where this can be very useful is to identify jobs that access a particular database table if it needs to be altered or maintained, for example, and you need this information to assess impacts.

Well, to be able to automate this, I created a SP that will scan all databases in your instance (using sp_msforeachdb), reading the sys.sql_modules view for each bank. After identifying all the SP's or functions that have the specific string you are looking for, the routine crosses this information with the views of the SQL Agent to identify which jobs call these SP's.

SP source code

To perform this check, simply use the source code below, which is also available in this GitHub. Feel free to suggest changes and improvements. One is that the SP below works when the job calls an SP, which calls another SP and this yes, has the string we are looking for.

Example of use:

That's it folks! I hope you enjoyed this SP, a hug and until the next post.