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.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.
DECLARE @proc AS VARCHAR(50)
SET @proc = 'card_vendor_class_reset' --specify stored procedure name here
step.job_id AS 'JobID',
step.command AS 'Source'
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.sysjobsteps step
ON step.job_id = job.job_id
WHERE step.command LIKE '%' + @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
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.
EXEC msdb.dbo.sp_update_job @job_name = 'ZOWNER/GW Class Reset',
@enabled = 0
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.