A Script A Day - Day 23 - Find an Object In Job Steps

Today’s script is one which saves me a lot of time on occasion.  On large systems with lets say more than 100 jobs it is esential to manage them properly and the smallest thing like naming conventions help a lot.  So what if you want to check if an object lets say a stored procedure is executed in a job, you aren’t going to search through all 100+ jobs and potentially 1000’s of job steps are you?  Hell No, you use a script, because we don’t like GUI’s ;)

The script below is simple enough just the one join from sysjobs to sysjobsteps and the only thing you need to do is to update the ObjectHere.  It is easy to see that you could use this script to search for any text in a job step not just an object!

/*

      -----------------------------------------------------------------

      Find an Object in Job Steps

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change database context

USE msdb;

GO

-- Find object in a job step

SELECT

      sj.name,

      sj.[enabled],

      sjs.step_id,

      sjs.step_name,

      sjs.command

FROM    msdb.dbo.sysjobs sj

      INNER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id

WHERE sjs.command LIKE '%ObjectHere%';

GO

Enjoy!

Chris