Menu
Is there a way to find out what jobs are using a certain stored procedure?
204
Using SQL Server Management Studio Expand SQL Server Agent. Right-click Job Activity Monitor and click View Job Activity. In the Job Activity Monitor, you can view details about each job that is defined for this server.
It contains the history of the SQL agent jobs that run and perform operations on the server. Each row of the table corresponds to a step in the job. There is a column in the table called run_status, that gets an integer value to indicate the outcome of the job when it completed and got written into the table.
To view the job history log Expand SQL Server Agent, and then expand Jobs. Right-click a job, and then click View History. In the Log File Viewer, view the job history. To update the job history, click Refresh.
This will capture instances where the procedure is explicitly referenced in the job step:
SELECT j.name FROM msdb.dbo.sysjobs AS j WHERE EXISTS ( SELECT 1 FROM msdb.dbo.sysjobsteps AS s WHERE s.job_id = j.job_id AND s.command LIKE '%procedurename%' ); If it is called by something else that is called from the job, or the command is constructed with dynamic SQL, this might be a little more difficult to track down. Note also that if your procedure name can also appear naturally in other code, comments, etc. that it may produce false positives.
50
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
