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

Rate

Share

Share

Rate