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
where js.command like '%' + @object_name + '%'
This will list names of all jobs which reference the object which can be procedure or dts package