Report upcoming SQL Jobs

if object_id('usp_upcoming_jobschedules') is not null
 drop procedure usp_upcoming_jobschedules
go

create procedure usp_upcoming_jobschedules(@min_runtime int = 1500, @category sysname = '%', @jobname sysname = '%')
/*
 name usp_upcoming_jobschedules
 author wilfred van dijk (wvand@wilfredvandijk.nl)

 purpose reports upcoming SQL jobs within a timeframe:
 - for monday-thursday timeframe is 1 day
 - for friday, timeframe is 3 days

 params @min_runtime minimum average runtime for jobs to be reported
 format is hhmmss (3000 means 30 minutes)
 @category filter on category, default: all categories
 @jobname fileter on jobname, default: all jobs

 version 20081107 initial release
*/as
 begin
 set nocount on
 set datefirst 7 /* sunday is first day */
 select left(d.name,16) as Category
 , left(b.name,50) as Jobname
 , left(e.name,50) as Schedule
 , substring('SunMonTueWedThuFriSat', datepart(weekday, cast(a.next_run_date as varchar))*3-2,3) as Day
 , left(right('000000' + cast(a.next_run_time as varchar),6),2) + ':' +
 substring(right('000000' + cast(a.next_run_time as varchar),6),3,2) as Time
                , c.run_duration "Average Time"
 from msdb.dbo.sysjobschedules a
 join msdb.dbo.sysjobs b
 on a.job_id = b.job_id
 join (select job_id, avg(run_duration) as run_duration from msdb.dbo.sysjobhistory group by job_id) c
 on b.job_id = c.job_id
 join msdb.dbo.syscategories d
 on b.category_id = d.category_id
 join msdb.dbo.sysschedules e
 on a.schedule_id = e.schedule_id
 where a.next_run_date
 between convert(varchar,getdate(),112)
 and convert(varchar,dateadd(day, case datepart(weekday, getdate()) when 6 then 3 else 1 end,getdate()),112)
 and b.name like @jobname
 and d.name like @category
 and c.run_duration > @min_runtime
 order by next_run_date, next_run_time
 end
go

/* New versioning as extended property */exec sp_addextendedproperty @name='version', @value='20081107', @level0type=N'schema', @level0name=N'dbo', @level1type='procedure', @level1name=N'usp_upcoming_jobschedules'
go