Search SQL Agent Job Steps

I’ve never really that about how enigmatic SQL Agent jobs can be because I am usually the one creating them. Anyways, a developer was asking if I knew a way to search the T-SQL within a job, so here it is …

DECLARE
      @SearchString varchar(255),
      @IsEnabled bit;
 
SET @SearchString = 'warp'; -- Enter Search String Here. Leave Blank for All
SET @IsEnabled = 1; -- 0 = Disabled, 1 = Enabled, 2 = All
 
SELECT
      j.Name JobName,
      j.Description JobDescription,
      js.step_id StepID,
      js.step_name StepName,
      js.database_name DatabaseName,
      js.command StepCommand  
FROM
      msdb..sysjobs j
INNER JOIN
      msdb..sysjobsteps js ON
            j.job_id = js.job_id
WHERE
      (j.enabled = @IsEnabled OR @IsEnabled = 2) AND
      js.command LIKE '%' + @SearchString + '%'
ORDER BY
      j.Name,
      js.step_id;

~Ron

Advertisements

2 Responses to “Search SQL Agent Job Steps”


  1. 1 philhege September 30, 2009 at 3:00 pm

    Thanks for this one, Ron; very handy. I had to look through about 50 jobs to identify dependencies, and of course the job creators used ad hoc SQL in the steps.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: