Oh what job was it? A procedure to search your jobs for a particular string

After regularly querying job data from the system tables I decided to write a very simple procedure that could be used to reduce the time taken rewriting the same TSQL multiple times a day.

The following procedure allows you to pass in string of text which well then be used to return any jobs that contain an instance of that particular string. The procedure will interrogate the job name, the job steps, and the contents of the step.

The results are in order of the job name, followed by each of the job steps that contain the string. Please feel free to amend/improve/expand the procedure, but if possible, let me know what you have done (for my own interest!).

/****** Object: StoredProcedure [dbo].[sp_SearchInJobs] Script Date: 12/08/2009 09:35:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[sp_SearchInJobs]

@texttofind nvarchar(255)

/************************************************************************************************************

**************************************************IMPORTANT**************************************************

** Purpose: Searches names and content of jobs and

** job steps for a provided string of text.

** Notes: from http://www.sqlservercentral.com/blogs/james_howards_sql_blog/

** Created By: James Howard

** Created On: 08th December 2009

************************************************************************************************************/

AS

SET NOCOUNT ON

SET @texttofind='%'+@texttofind+'%'

SELECT j.name AS JobName

,case j.enabled

when 0 then 'No'

when 1 then 'Yes'

else 'UNKNOWN'

END as enabled

,js.step_id AS StepNum

,js.step_name AS StepName

,js.subsystem AS Type

,js.command AS StepContent

,js.database_name AS db

,js.last_run_date AS LastRun

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobsteps js

ON j.job_id=js.job_id

WHERE (j.name like @texttofind

OR js.step_name like @texttofind

OR js.command like @texttofind)

ORDER BY j.job_id,js.job_id

GO