Getting the Job Name Based on Step Command

Last month, I debugged a procedure that I assumed was the faulty step of a job. Well, I was quite sure that it is part of the schedule. But, I got no idea what job it belongs to. Knowing the job name was necessary for me to further understand and relate to the problem and the need to possibly rerun the step.

So, let me share to you a simple way of instantly determining the job name through searching for the step command (probably in Procedure call statement) in case no comprehensive documentation is available.

SELECT j.name FROM msdb..sysjobs AS j
INNER JOIN msdb..sysjobsteps AS s
ON s.job_id = j.job_id
WHERE s.command LIKE '%my_procedure%'

The command search is not limited to procedure call statements. It could also be a string of code/logic, thus, using the LIKE statement would be very suitable because you can just supply a snippet of its entirety.