WHEN 128 THEN 'Start whenever the CPU(s) become idle' END as Occurs WHEN 64 THEN 'When SQL Server Agent starts' , CASE s.enabled WHEN 1 THEN 'Yes' else 'No' END as Scheduled , CASE j.enabled WHEN 1 THEN 'Yes' else 'No' END as Enabled SELECT = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)ĮXECUTE _sqlagent_enum_jobs AS JobName Request_source_id sysname COLLATE database_default NULL, IF OBJECT_ID('tempdb.#xp_results') IS NOT NULL CREATE PROCEDURE sp_job_list VARCHAR(100)=NULL In case you don't pass any value, it will return the details of all jobs. JOIN as jhist on job.job_id = jhist.job_id and jhist.step_id = 0 + SubString(sched.nextRunTime, 3, 2) As 'Next Run Date / Time' + SubString(sched.nextRunTime, 1, 2) + ':' + SubString(sched.nextRunDate, 7, 2) + ' ' + SubString(sched.nextRunDate, 5, 2) + '/' , SubString(sched.nextRunDate, 1, 4) + '/' + SubString(sched.startTime, 3, 2) + ' - ' On sched.schedule_id = jobsched.schedule_id , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate' + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime' , Replicate('0', 6 - Len(jobsched.next_run_time)) + Cast(sched.active_end_time As varchar(6)) As 'endTime'
, Replicate('0', 6 - Len(sched.active_end_time)) + Cast(sched.active_start_time As varchar(6)) As 'startTime' , Replicate('0', 6 - Len(sched.active_start_time)) , IsNull('Every ' + Cast(eq_subday_interval As varchar(10)) +Ĭase When eq_subday_type = 2 Then ' seconds' + Cast(eq_recurrence_factor As varchar(10)) + ' months' End When eq_interval = 10 Then ' Weekend'Īnd eq_recurrence_factor = 1 Then ' monthly' When eq_relative_interval = 16 Then 'Last'Ĭase When eq_interval = 1 Then ' Sunday' When eq_relative_interval = 8 Then 'Fourth' When eq_relative_interval = 4 Then 'Third' When eq_relative_interval = 2 Then 'Second' + Cast(eq_recurrence_factor As varchar(10)) + ' months'Ĭase When eq_relative_interval = 1 Then 'First' + Cast(eq_interval As varchar(10)) + ' of every ' + Cast(eq_recurrence_factor As varchar(10)) + ' weeks' End When eq_recurrence_factor 0 Then ' every ' Then 'Every ' + Cast(eq_interval As varchar(5)) + ' days' This will get all enabled jobs on your SQL Server and return, (Job Name, Schedule Name, Frequency, Sub Frequency, Schedule Time, Next Run Date / Time and Last Run Status) Declare Table ( Left join SS on SS.schedule_id = SJ.schedule_id WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes' WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds' 'freq_rel' = CASE(freq_relative_interval) WHEN (freq_type = 32) then (select freq_rel + WDAY WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval) 'D7' = CASE WHEN (freq_interval & 64 0) then 'Sat ' ELSE '' END 'D6' = CASE WHEN (freq_interval & 32 0) then 'Fri ' ELSE '' END, 'D5' = CASE WHEN (freq_interval & 16 0) then 'Thu ' ELSE '' END, 'D4' = CASE WHEN (freq_interval & 8 0) then 'Wed ' ELSE '' END, 'D3' = CASE WHEN (freq_interval & 4 0) then 'Tue ' ELSE '' END, 'D2' = CASE WHEN (freq_interval & 2 0) then 'Mon ' ELSE '' END, 'D1' = CASE WHEN (freq_interval & 1 0) then 'Sun ' ELSE '' END, WHEN (freq_type = 8) then (select 'Weekly Schedule' = MIN(D1+ D2+D3+D4+D5+D6+D7 ) WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days' WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day' WHEN (freq_type = 1) then 'One time only'
WHEN 32 THEN 'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Months' - RELATIVE WHEN 16 THEN (case when (SS.freq_recurrence_factor > 1) then 'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Months' else 'Monthly' end) WHEN 8 THEN (case when (SS.freq_recurrence_factor > 1) then 'Every ' + convert(varchar(3),SS.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)