/***	Scheduled task reporter for SQL 2000
****	Copyright Neil Boyle 2003. 
****	Use entirely at your own risk
***/
use MSDB
go

select    convert(varchar(22), j.name) as job_name
,    case freq_type  -- Daily, weekly, Monthly
		when 1    then 'Once'
		when 4    then 'Daily'
		when 8    then 'Wk ' -- For weekly, add in the days of the week
		+    case      freq_interval & 2 when 2 then 'M' else '' end  -- Monday
		+    case      freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
		+    case      freq_interval & 8 when 8 then 'W' else '' end  -- etc
		+    case      freq_interval & 16 when 16 then 'Th' else '' end
		+    case      freq_interval & 32 when 32 then 'F' else '' end
		+    case      freq_interval & 64 when 64 then 'Sa' else '' end
		+    case      freq_interval & 1 when 1 then 'Su' else '' end
		when 16   then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day
		when 32   then 'Mthly '  -- The most complicated one, "every third Friday of the month" for example
		+ case freq_relative_interval 
			when 1 then 'Every First '
			when 2 then 'Every Second '
			when 4 then 'Every Third '
			when 8 then 'Every Fourth '
			when 16 then 'Every Last '
		end
		+ case 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 64   then 'Startup'	-- When SQL Server starts
	when 128 then 'Idle'		-- Whenever SQL Server gets bored
	else 'Err'			-- This should never happen
	end as schedule

,	case freq_subday_type		-- FOr when a job funs every few seconds, minutes or hours
		when 1    then 'Runs once at:'
		when 2    then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds'
		when 4    then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes'
		when 8    then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
	end as frequency

	-- All the subsrings are because the times are stored as an integer with no leading zeroes
	-- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00)

,    	substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2)
	+    ':'
	+ substring (
	right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2)
	+    ':'
	+ substring (
	right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at

,case	freq_subday_type
	when 1  then NULL  -- Ignore the end time if not a recurring job
	else	substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2)
	+    ':'
	+ substring (
	right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2)
	+    ':'
	+ substring (
	right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at
from	sysjobs j
join 	sysJobSchedules s
on   	j.job_id = s.job_id
order	by j.name, start_at