SQL Server 查找正在运行过程的作业

问题描述：

有没有办法找出哪些作业正在使用某个存储过程?

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

答

这将捕获在作业步骤中显式引用过程的实例:

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%'
  );

如果它被从作业调用的其他东西调用，或者命令是用动态 SQL 构造的，这可能会更难以追踪.另请注意，如果您的过程名称也可以自然地出现在其他代码、注释等中，则可能会产生误报.

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.

SQL Server 查找正在运行过程的作业

相关推荐