View Jobs Schedule Information

The code portion given below is useful when you need to get the schedule information of SQL Server jobs. There are several system tables exist in msdb database to retrieve jobs related information. But in this scenario I wanted to see the jobs schedule description which displays in jobs properties UI.

The description is not stored in any system tables as is rather it will do a manipulation by using other values. 


It will use the system SP, sp_get_schedule_description   to prepare description. 



create table #temp_jobschedule
(
job_id uniqueidentifier
,schedule_id int
,schedule_name sysname
,[enabled] int
,freq_type int
,freq_interval int
,freq_subday_type int
,freq_subday_interval int
,freq_relative_interval int
,freq_recurrence_factor int
,active_start_date int
,active_end_date int
,active_start_time int
,active_end_time int
,date_created datetime
,schedule_description nvarchar(4000)
,next_run_date int
,next_run_time int
,schedule_uid uniqueidentifier
,job_count int

)




declare @Job_Id uniqueidentifier
declare @Last_Job_Id uniqueidentifier


select top 1 @Job_Id=J.job_id
from msdb..sysjobs j
where j.enabled=1
and j.category_id not in (10,15,13)
order by J.job_id


select top 1 @Last_Job_Id=J.job_id
from msdb..sysjobs j
where j.enabled=1
and j.category_id not in (10,15,13)
order by J.job_id desc


while @Job_Id is not null
begin


insert into #temp_jobschedule (schedule_id,schedule_name,[enabled],freq_type,freq_interval,freq_subday_type,freq_subday_interval,freq_relative_interval,freq_recurrence_factor
,active_start_date,active_end_date,active_start_time,active_end_time,date_created,schedule_description,next_run_date,next_run_time,schedule_uid,job_count)
exec sp_executesql N'exec msdb..sp_help_jobschedule @job_id=@JOB_ID,@include_description=1',N'@JOB_ID uniqueidentifier', @JOB_ID=@Job_Id

select top 1 @Job_Id=J.job_id
from msdb..sysjobs j
where j.[enabled]=1
and j.category_id not in (10,15,13)
and J.job_id>@Job_Id
order by J.job_id

if @Job_Id=@Last_Job_Id
set @Job_Id=null


end


update #temp_jobschedule set job_id=jsch.job_id
from msdb.dbo.sysjobschedules jsch
where #temp_jobschedule.schedule_id=jsch.schedule_id


select j.name,schedule_description,jsrv.last_run_duration
from #temp_jobschedule jsch
inner join msdb.dbo.sysjobs j
on jsch.job_id=j.job_id
inner join msdb.dbo.sysjobservers jsrv
on jsch.job_id=jsrv.job_id