CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Lloyd Banks
Lloyd Banks

Reputation: 36689

SQL Server Find What Jobs Are Running a Procedure

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

Upvotes: 44

Views: 104632

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280570

This will capture instances where the procedure is explicitly referenced in the job step:

DECLARE @s nvarchar(255) = N'procedure_name';

SET @s = QUOTENAME(@s, nchar(37));

SELECT job_name = j.name,
       s.step_id,
       s.step_name,
       j.date_created,
       j.date_modified
  FROM msdb.dbo.sysjobs AS j
  INNER JOIN msdb.dbo.sysjobsteps AS s
  ON j.job_id = s.job_id
      WHERE s.command   LIKE @s
         OR j.name      LIKE @s
         OR s.step_name LIKE @s;

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.

A much more elaborate and flexible search procedure that I updated last year:

And even better is this module in AMtwo's DBA Database:

Upvotes: 93

Related Questions