We have hundreds of system as well as customize job setup in SQL Server Agent for our production databases. It is hard to find one particular job out of this long list. We tend to maintain document for each job we set but I always try to find solution right from the SSMS if it is a question of SQL Server.
It happens many time that I got SP name and have to find SQL Server Job(s) which is calling that particular stored procedure. It is not at all feasible for me to go through each and every SQL Server Job, open it’s step and find SP/Table is being called from it.
Following small SELECT, based on SysJobs and SysJobSteps system view , script always come as a rescue in this kind of situation.
USE MSDB GO SELECT JobStep.Database_Name, SysJob.name AS Job_Name, JobStep.command, JobStep.step_id, JobStep.Last_Run_Date, JobStep.Last_Run_Time, JobStep.Last_Run_Duration FROM sysjobs AS SysJob INNER JOIN sysjobsteps JobStep ON SysJob.job_id = JobStep.job_id WHERE JobStep.command like '%You_SP_or_Table_Name%' GO
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.