Get scheduled jobs
The script is not as complete (and complex) as the USP_GetJobSchedules from M.Pearson, M. Austin and R.Sotkiewicz, but it gives a list of the scheduled jobs with start time, name, frequency, category and whether is enabled or not
use msdb
select
"Start Time" =
case
when s.active_start_time >= 100000 then substring(convert(varchar,active_start_time),1,2) + ':' + substring(convert(varchar,active_start_time),3,2)+ ':' + substring(convert(varchar,active_start_time),5,2)
when s.active_start_time < 100000 and active_start_time >= 10000 then substring(convert(varchar,active_start_time),1,1) + ':' + substring(convert(varchar,active_start_time),2,2)+ ':' + substring(convert(varchar,active_start_time),3,2)
when s.active_start_time < 10000 then '00:' + substring(convert(varchar,active_start_time),1,2) + ':' + substring(convert(varchar,active_start_time),3,2)
end,
s.name as Name,
case s.freq_type
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly relative'
when 64 then 'When SQL Agent starts'
end as Frequency,
c.name as Category,
case s.enabled
when 1 then 'Yes'
when 0 then 'No'
end as Enabled
from
sysjobschedules s,
sysjobs j,
syscategories c
where
s.job_id = j.job_id
and j.category_id = c.category_id
order by
s.active_start_time
