Listing Jobs names and Schedule

Hi,
This is a script to detail the job names and schedule.

Script:
use msdb
Select Job.job_name, Job.category,

CASE
WHEN Job.enabled=0 THEN 'INACTIVE'
WHEN Job.enabled=1 THEN 'ACTIVE'
END AS STATUS

,D.schedule
from
(select j.name as job_name,c.name as category,j.enabled ,j.job_id
from sysjobs j
join syscategories c on c.category_id=j.category_id
) as Job
join sysjobschedules sc on sc.job_id = Job.job_id
join
(SELECT s.schedule_id,
s.[name],
CASE
WHEN s.freq_type = 0x1
THEN
'Once on '
+ CONVERT (
CHAR (10),
CAST (CAST (s.active_start_date AS VARCHAR) AS DATETIME),
102)
WHEN s.freq_type = 0x4 AND s.freq_interval > 0
THEN
CASE
WHEN s.freq_interval > 1
THEN
'Every ' + CAST (s.freq_interval AS VARCHAR) + ' days'
ELSE
'Every day'
END
WHEN s.freq_type = 0x8
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Weekly on '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' weeks on '
END
+ LEFT (
CASE
WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END,
LEN (
CASE
WHEN s.freq_interval & 1 = 1 THEN 'Sunday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 2 = 2 THEN 'Monday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 16 = 16 THEN 'Thursday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 32 = 32 THEN 'Friday, '
ELSE ''
END
+ CASE
WHEN s.freq_interval & 64 = 64 THEN 'Saturday, '
ELSE ''
END)
- 1)
WHEN s.freq_type = 0x10
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' months on the '
END
+ CAST (s.freq_interval AS VARCHAR)
+ CASE
WHEN s.freq_interval IN (1, 21, 31) THEN 'st'
WHEN s.freq_interval IN (2, 22) THEN 'nd'
WHEN s.freq_interval IN (3, 23) THEN 'rd'
ELSE 'th'
END
WHEN s.freq_type = 0x20
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN
'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN
'Every '
+ CAST (s.freq_recurrence_factor AS VARCHAR)
+ ' months on the '
END
+ CASE s.freq_relative_interval
WHEN 0x01 THEN 'first '
WHEN 0x02 THEN 'second '
WHEN 0x04 THEN 'third '
WHEN 0x08 THEN 'fourth '
WHEN 0x10 THEN 'last '
END
+ CASE s.freq_interval
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'day'
WHEN 9 THEN 'week day'
WHEN 10 THEN 'weekend day'
END
WHEN s.freq_type = 0x40
THEN
'Automatically starts when SQLServerAgent starts.'
WHEN s.freq_type = 0x80
THEN
'Starts whenever the CPUs become idle'
ELSE
''
END
+ CASE
WHEN s.freq_subday_type = 0x1 OR s.freq_type = 0x1
THEN
' at '
+ LEFT (s.active_start_time, 2)
+ ':'
+ substring (s.active_start_time, 3, 2)
+ ':'
+ right (s.active_start_time, 2)
WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)
THEN
' every '
+ CAST (s.freq_subday_interval AS VARCHAR)
+ CASE freq_subday_type
WHEN 0x2 THEN ' second'
WHEN 0x4 THEN ' minute'
WHEN 0x8 THEN ' hour'
END
+ CASE WHEN s.freq_subday_interval > 1 THEN 's'
ELSE ' '
END
ELSE
' '
END
+ CASE
WHEN s.freq_subday_type IN (0x2, 0x4, 0x8)
THEN
' between '
+ LEFT (s.active_start_time, 2)
+ ':'
+ substring (s.active_start_time, 3, 2)
+ ':'
+ right (s.active_start_time, 2)
+ ' and '
+ LEFT (s.active_end_time, 2)
+ ':'
+ substring (s.active_end_time, 3, 2)
+ ':'
+ right (s.active_end_time, 2)
ELSE
''
END
AS schedule
FROM (
SELECT schedule_id,
[name],
freq_type,
freq_interval,
freq_subday_type,
freq_subday_interval,
freq_relative_interval,
freq_recurrence_factor,
active_start_date,
active_end_date,
REPLICATE ('0', 6 - len (cast (active_start_time AS VARCHAR)))
+ cast (active_start_time AS VARCHAR)
AS active_start_time,
REPLICATE ('0', 6 - len (cast (active_end_time AS VARCHAR)))
+ cast (active_end_time AS VARCHAR)
AS active_end_time,
date_created,
date_modified,
version_number
FROM msdb.dbo.sysschedules) AS s ) as D on D.schedule_id=sc.schedule_id


Refernence: sqlservercentral.com

Tushar