Hey, everybody! So a few years ago during a webinar by Chuck Reif, we were given the gift of sp_what_proc -- a stored procedure I use at least five times a week, possibly more, to figure out what procedures in my system use a particular piece of text. It's super-handy.
Well, this morning I found myself looking through alllll the SQL Server Agent jobs in my SQL Server Management Studio to figure out which ones make reference to our last season's season_no when executing commands. I was wishing there were a way to quickly search them all (like with sp_what_proc) rather than opening the properties of each and opening the steps to see what each does
Maybe there's some built in way to do that which I don't know about? Or maybe somebody else has already written something like this. BUT I have written a solution, analogous to sp_what_proc. Here's sp_what_job -- put it into your database and enjoy zippy answers to your "which job does this again" questions from now on! You can just go, for example,
exec sp_what_job '31'
and it'll spit back every job and step that has '31' (our 2010-11 season number) as part of the step's commands.
USE [impresario]
GO
/****** Object: StoredProcedure [dbo].[SP_WHAT_JOB] Script Date: 03/29/2011 16:01:05 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE procedure [dbo].[SP_WHAT_JOB](
@textstring varchar(255))
AS
/* Andrea Crain, Chicago Shakespeare Theater, 3/29/2011
based on sp_what_proc
searches SQL Server Agent Jobs for the text string provided. */
declare @sqlstring varchar(255)
select @sqlstring = 'select job.name as "job_name",
step.step_name, step.command
from msdb.dbo.sysjobsteps step
JOIN msdb.dbo.sysjobs job ON step.job_id = job.job_id
where step.command like ''%' + @textstring + '%'''
exec (@sqlstring)