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

Views: 161
Reading Time: 4 minutes

Hello people,

In this post I will share with you a need I had on Friday, where a file server would be down for a few hours for maintenance and needed to identify which jobs were accessing that server and therefore would be impacted by this maintenance, ie fetch a string from the code of SP's 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 automate this, I created an SP that will scan all the databases of your instance (using sp_msforeachdb), reading the sys.sql_modules view of each database. After identifying all SPs or functions that have the particular string you are looking for, the routine crosses that information with SQL Agent views to identify which jobs call those 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.