hx_JobSchedules

drop PROCEDURE hx_JobSchedules
go

/* Robert Vallee 08/01/2001
rvallee@hybridx.com
input:None
output:Table format
Desc:Displays Job Schedules info if any.
Warnings: None.
*/CREATE PROCEDURE hx_JobSchedules AS

SELECT DISTINCT -- I use distinct only for readability
substring(msdb..sysjobs.name,1,100) AS [Job Name], 
'Enabled'=case 
WHEN msdb..sysjobs.enabled = 0 THEN 'No'
WHEN msdb..sysjobs.enabled = 1 THEN 'Yes'
end, 
    substring(msdb..sysjobschedules.name,1,30) AS [Name of the schedule],
'Frequency of the schedule execution'=case
WHEN msdb..sysjobschedules.freq_type = 1 THEN 'Once'
WHEN msdb..sysjobschedules.freq_type = 4 THEN 'Daily'
WHEN msdb..sysjobschedules.freq_type = 8 THEN 'Weekly'
WHEN msdb..sysjobschedules.freq_type = 16 THEN 'Monthly'
WHEN msdb..sysjobschedules.freq_type = 32 THEN 'Monthly relative'
WHEN msdb..sysjobschedules.freq_type = 32 THEN 'Execute when SQL Server Agent starts'
END,
'Units for the freq_subday_interval'=case
WHEN msdb..sysjobschedules.freq_subday_type = 1 THEN 'At the specified time' 
WHEN msdb..sysjobschedules.freq_subday_type = 2 THEN 'Seconds' 
WHEN msdb..sysjobschedules.freq_subday_type = 4 THEN 'Minutes' 
WHEN msdb..sysjobschedules.freq_subday_type = 8 THEN 'Hours' 
END,
cast(cast(msdb..sysjobschedules.active_start_date as varchar(15)) as datetime) as active_start_date,
cast(cast(msdb..sysjobschedules.active_end_date as varchar(15)) as datetime) as active_end_date,
cast(cast(msdb..sysjobschedules.next_run_date as varchar(15)) as datetime) as next_run_date,
msdb..sysjobschedules.next_run_time,
msdb..sysjobschedules.date_created

FROM msdb..sysjobhistory INNER JOIN
       msdb..sysjobs ON 
       msdb..sysjobhistory.job_id = msdb..sysjobs.job_id INNER JOIN
       msdb..sysjobschedules ON msdb..sysjobs.job_id = msdb..sysjobschedules.job_id