Searching for table inforation in SQL jobs

I'm putting this here to make it easier to look at job and job step information quickly.


USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[JobInformation_SqlSSISJobs_SelectBySSISFileName]
Script Date: 06/24/2009 13:32:21 ******

This script is used to return every job step where a string occurs

*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[JobInformation_SqlSSISJobs_SelectBySSISFileName]
@fileName as varchar(125)
AS

/*
declare @fileName as varchar(125)
set @fileName = 'Order Invoice Import Data.dstConfig'
*/

select
[name] as JobName
, step.step_id as JobStepNbr
, step.step_name as JobStepName
, subsystem as JobStepType
, command as JobStepCommand
, database_name AS JobStepDbName
, last_run_date AS JobStepLastRunDate
, last_run_time AS JobStepLastRunTime
, next_run_date AS JobStepNextRunDate
, next_run_time AS JobStepNextRunTime
, run_status AS JobStepLastRunStatus
--, hist.*
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.sysjobsteps step
ON job.job_id = step.job_id
INNER JOIN msdb.dbo.sysjobschedules sch
ON job.job_id = sch.job_id
LEFT OUTER JOIN dbo.sysjobhistory hist
ON job.job_id = hist.job_id
AND step.step_id = hist.step_id
AND step.last_run_date = hist.run_date
AND step.last_run_time = hist.run_time
WHERE command like '%' + @fileName + '%';



----------------
Now playing: NPR - 0906171: NPR: 06-20-2009 Wait Wait... Don't Tell Me!
via FoxyTunes