There is a stored procedure being executed daily which inserts a record in a specific table. Our Production database servers has lot of SQL jobs scheduled to run daily at different intervals.
How to find out which job is running this particular stored procedure? Is there a way to find using SQL scripts?
You need to search the
command column in the msdb
sysjobsteps table of each server.
USE msdb SELECT * FROM sysjobsteps WHERE command LIKE '%stored_procedure_name%'
Once you find the job step that is running it, you'll use the
job_id column to find the associated job in the
sysjobs table. See dbo.sysjobsteps.
I typically use a PowerShell script to iterate through all of my servers when I need to run something like this on all servers. That is a bit beyond the scope of this question, though.