Query msdb System Tables To Find A Procedure Called By An Agent Job

I recently assisted with a database cleanup project which involved disabling a handful of retired SQL agent jobs as well as archiving quite a few obsolete stored procedures. One of our analyst was working to determine which procedures could be safely removed.  However, due to the large volume of agent jobs residing on the database he needed a way to determine if any of the procedures were currently being called by any of the agent jobs.

Since opening each job and reviewing the individual steps within the job was proving to be quite time consuming, I decided to write a quick script to query the msdb.dbo.sysjobs and msdb.dbo.sysjobsteps system tables which stores all the data pertaining to agent jobs.

The following script allows you to specify a stored procedure name and will search all agent jobs on the server for that proc.

The script takes @proc parameter provided by the user and references the sysjobsteps table to check the command column for the specified value.  This column stores the command being called by an individual step within an agent job.

An example of this is shown below. In this case I am searching for a proc named card_vendor_class_reset.

job_search_script_output

As you can see there is a single agent job, ZOWNER/GW Class Reset, that calls this stored procedure. In most cases you should only have a single job calling a particular procedure but there are situations where you may leverage the same stored procedure for multiple agent jobs.

We can take this a step further.  Since the purpose of this project was to disable certain jobs and stored procedures, for any of the procedures on the list that were also assigned to a job we can easily disable the job.  Using the built in procedure sp_update_job we can disable the job based on the name field above.

In this example I am disableing the ZOWNER/GW Class Reset job.  This is a much faster approach than manually disabling the jobs in Management Studio.