42

Is there a way to find out what jobs are using a certain stored procedure?

Lloyd Banks
  • 34,109
  • 54
  • 152
  • 238

1 Answers1

87

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.

Aaron Bertrand
  • 261,961
  • 36
  • 448
  • 471
  • 1
    Slight modification to this: `DECLARE @Search varchar(255) SET @Search='searchstring' SELECT j.name, s.step_id, s.step_name FROM msdb.dbo.sysjobsteps AS s, msdb.dbo.sysjobs as j WHERE s.job_id = j.job_id AND s.command LIKE '%'+@Search+'%'` – Goner Doug Apr 21 '17 at 20:27