Friday, March 18, 2011

Search for a string in SQL Agent Job Steps



When you change schema of tables or
when you change a SP, you probably need to
to find out whether the SP used in SQL Server
Job(s) or there are inline SQL statements in
the Jobs that use your table(s).






How to do it quickly?

SELECT  js.database_name as DatabaseName,
                 jobs.Name as JobName,
                 js.step_id as StepID,
                 js.step_name as StepName,
                 js.command as StepCommand
FROM     msdb.dbo.sysjobs as jobs
                INNER JOIN msdb.dbo.sysjobsteps as js ON jobs.job_id = js.job_id
WHERE  jobs.[enabled] = 1 AND js.command LIKE  '%XXXXXXXXX%'
ORDER BY jobs.Name,js.step_id

Thanks for reading.

4 comments:

  1. AWESOME! This is a big help!

    ReplyDelete
  2. Perfect, thanks. :)

    ReplyDelete
  3. Exactly what I was needing. Thanks

    ReplyDelete