Finding object reference within jobs

At last I'm back! its been sometime since I blogged. For those who're thinking where I'd been all this time, I was off to China for project implementation. Unfortunately blogspot was unavailable there which kept me away from blogs.

Now that I'm back thought of resuming my blogging. This is a small blog to mark my comeback.

Now onto subject. Quite often we've come across situations where we need to search for reference of particular object within jobs. For ex: consider case where we want to search for which all jobs use a particular procedure we have written. The easiest way to get this information is by querying the job system tables sysjobs and sysjobsteps in msdb.

declare @object_name varchar(500)

set @object_name= 'TestSP' -- your object name to be searched here

select j.name from msdb.dbo.sysjobs j

inner join msdb.dbo.sysjobsteps js

on js.job_id=j.job_id

where js.command like '%' + @object_name + '%'

This will list names of all jobs which reference the object which can be procedure or dts package