RE: Display Job Schedule Description (SQL Server 2005)

Mr or Mrs. 500

Points: 575

October 24, 2012 at 5:51 pm

For those who want to run this script in SQL Server 2000, here is a slightly modified version that doesn't use msdb.dbo.sysschedules. All the scheduling columns were present in msdb.dbo.sysjobschedules system table in SQL Server 2000.

-- Query to display schedules of jobs.

-- copied from http://www.sqlservercentral.com/scripts/Maintenance+and+Management/62222/

-- posted there by "Cowboy DBA"

-- ----------

-- Revised 3/21/08 John Arnott

-- added default "ELSE" to case that would put plural "S" in schedule description. (find comment " -- Added default 3/21/08; John Arnott"

-- Revised 6/19/08 John Arnott

-- re-coded the time parsing routines. Old code didn't handle first hour of the day (00:04, for instance).

-- new code uses division by power of 10 to isolate hour, then minute portion of time,

-- then a little trick with REPLICATE to left-fill the minutes with a zero if needed.

SELECT

@@servername as [Server]

, msdb.dbo.sysjobs.name as [JobName]

, CASE

WHEN msdb.dbo.sysjobs.enabled = 0 THEN 'Disabled'

WHEN msdb.dbo.sysjobs.job_id IS NULL THEN 'Unscheduled'

WHEN msdb.dbo.sysjobschedules.freq_type = 0x1 -- OneTime

THEN

'Once on '

+ CONVERT(

CHAR(10)

, CAST( CAST( msdb.dbo.sysjobschedules.active_start_date AS VARCHAR ) AS DATETIME )

, 102 -- yyyy.mm.dd

)

WHEN msdb.dbo.sysjobschedules.freq_type = 0x4 -- Daily

THEN 'Daily'

WHEN msdb.dbo.sysjobschedules.freq_type = 0x8 -- weekly

THEN

CASE

WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor = 1

THEN 'Weekly on '

WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor > 1

THEN 'Every '

+ CAST( msdb.dbo.sysjobschedules.freq_recurrence_factor AS VARCHAR )

+ ' weeks on '

END

+ LEFT(

CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END

, LEN(

CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END

+ CASE WHEN msdb.dbo.sysjobschedules.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END

) - 1 -- LEN() ignores trailing spaces

)

WHEN msdb.dbo.sysjobschedules.freq_type = 0x10 -- monthly

THEN

CASE

WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor = 1

THEN 'Monthly on the '

WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor > 1

THEN 'Every '

+ CAST( msdb.dbo.sysjobschedules.freq_recurrence_factor AS VARCHAR )

+ ' months on the '

END

+ CAST( msdb.dbo.sysjobschedules.freq_interval AS VARCHAR )

+ CASE

WHEN msdb.dbo.sysjobschedules.freq_interval IN ( 1, 21, 31 ) THEN 'st'

WHEN msdb.dbo.sysjobschedules.freq_interval IN ( 2, 22 ) THEN 'nd'

WHEN msdb.dbo.sysjobschedules.freq_interval IN ( 3, 23 ) THEN 'rd'

ELSE 'th'

END

WHEN msdb.dbo.sysjobschedules.freq_type = 0x20 -- monthly relative

THEN

CASE

WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor = 1

THEN 'Monthly on the '

WHEN msdb.dbo.sysjobschedules.freq_recurrence_factor > 1

THEN 'Every '

+ CAST( msdb.dbo.sysjobschedules.freq_recurrence_factor AS VARCHAR )

+ ' months on the '

END

+ CASE msdb.dbo.sysjobschedules.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 msdb.dbo.sysjobschedules.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 msdb.dbo.sysjobschedules.freq_type = 0x40

THEN 'Automatically starts when SQLServerAgent starts.'

WHEN msdb.dbo.sysjobschedules.freq_type = 0x80

THEN 'Starts whenever the CPUs become idle'

ELSE ''

END

+ CASE

WHEN msdb.dbo.sysjobs.enabled = 0 THEN ''

WHEN msdb.dbo.sysjobs.job_id IS NULL THEN ''

WHEN msdb.dbo.sysjobschedules.freq_subday_type = 0x1 OR msdb.dbo.sysjobschedules.freq_type = 0x1

THEN ' at '

+ Case -- Depends on time being integer to drop right-side digits

when(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000 = 0 then

'12'

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)))

+ convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)

+ ' AM'

when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000< 10 then

convert(char(1),(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)))

+ convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)

+ ' AM'

when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000 < 12 then

convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)))

+ convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)

+ ' AM'

when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000< 22 then

convert(char(1),((msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) - 12)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)))

+ convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)

+ ' PM'

elseconvert(char(2),((msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) - 12)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)))

+ convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)

+ ' PM'

end

WHEN msdb.dbo.sysjobschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 )

THEN ' every '

+ CAST( msdb.dbo.sysjobschedules.freq_subday_interval AS VARCHAR )

+ CASE freq_subday_type

WHEN 0x2 THEN ' second'

WHEN 0x4 THEN ' minute'

WHEN 0x8 THEN ' hour'

END

+ CASE

WHEN msdb.dbo.sysjobschedules.freq_subday_interval > 1 THEN 's'

ELSE '' -- Added default 3/21/08; John Arnott

END

ELSE ''

END

+ CASE

WHEN msdb.dbo.sysjobs.enabled = 0 THEN ''

WHEN msdb.dbo.sysjobs.job_id IS NULL THEN ''

WHEN msdb.dbo.sysjobschedules.freq_subday_type IN ( 0x2, 0x4, 0x8 )

THEN ' between '

+ Case -- Depends on time being integer to drop right-side digits

when(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000 = 0 then

'12'

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)))

+ rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))

+ ' AM'

when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000< 10 then

convert(char(1),(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)))

+ rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))

+ ' AM'

when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000 < 12 then

convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)))

+ rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))

+ ' AM'

when (msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000< 22 then

convert(char(1),((msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) - 12)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)))

+ rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))

+ ' PM'

elseconvert(char(2),((msdb.dbo.sysjobschedules.active_start_time % 1000000)/10000) - 12)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100)))

+ rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_start_time % 10000)/100))

+ ' PM'

end

+ ' and '

+ Case -- Depends on time being integer to drop right-side digits

when(msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000 = 0 then

'12'

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100)))

+ rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100))

+ ' AM'

when (msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000< 10 then

convert(char(1),(msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100)))

+ rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100))

+ ' AM'

when (msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000 < 12 then

convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100)))

+ rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100))

+ ' AM'

when (msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000< 22 then

convert(char(1),((msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000) - 12)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100)))

+ rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100))

+ ' PM'

elseconvert(char(2),((msdb.dbo.sysjobschedules.active_end_time % 1000000)/10000) - 12)

+ ':'

+Replicate('0',2 - len(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100)))

+ rtrim(convert(char(2),(msdb.dbo.sysjobschedules.active_end_time % 10000)/100))

+ ' PM'

end

ELSE ''

END AS Schedule

FROM msdb.dbo.sysjobs INNER JOIN

msdb.dbo.sysjobschedules ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobschedules.job_id

--where msdb.dbo.sysjobs.name like '%DCT%'

order by 2

[font="Tahoma"]Fahim Ahmed[/font]
[font="Times New Roman"]Knowledge is a journey, not a destiny [/font]