Recently we had come across a scenario where we needed to find list of sql jobs which are dependent on an object (a stored procedure in our case). We had a generic procedure which was used by several sql agent jobs and we had to replace it with a new procedure with some extended functionality. Now the question was how to find the jobs which are using this procedure. After analysis, we found out that the information can be captured from system table sysjob and sysjobsteps in msdb. I’m sharing the query we came up with so that it will benefit someone who has similar requirement.
use [msdb]
declare @objectname varchar(100)
set @objectname=your object name here
select j.[name] as jobname,c.[name],js.*
from dbo.sysjobs j
inner join dbo.sysjobsteps js
on js.job_id = j.job_id
inner join dbo.syscategories c
on c.category_id = j.category_id
where js.command like ‘% ‘ + @objectname + ‘ %’
notice the spaces along with % character. This is required for whole word matching else it will also return cases where it can find @objectname patterns within job step.
Similarly if you want to search for jobs which uses a particular SSIS package you just to need to remove spaces near %. this is because the command for ssis packages will come in pattern like that shown in 5 th point in below link
