— exec BD_Upcoming_JobSchedules @MinRuntime = 0

CREATE procedure BD_Upcoming_JobSchedules
(@MinRuntime int = 0, @category sysname = ‘%’, @jobname sysname = ‘%’)

as
begin
set nocount on
set datefirst 7

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/100 > @MinRuntime
order by next_run_date, next_run_time
end
go