Sunday, August 8, 2010

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