Hi .. check out the

exec msdb..sp_help_schedule default,default,default,1

on SqlServer 2005

but there is a bug in sp_get_schedule_description

it delivers the wrong description for dayly jobs with a interval of n days

eg: create a schedule to run every 3 days at 12:00:00

the sp will give you every day at 12:00:00 :w00t:

i wrote this one instead by using the core of the statement above 😎 :

SELECT s.schedule_id,

s.schedule_uid,

s.[name],

s.enabled,

s.freq_type,

s.freq_interval,

s.freq_subday_type,

s.freq_subday_interval,

s.freq_relative_interval,

s.freq_recurrence_factor,

s.active_start_date,

s.active_end_date,

s.active_start_time,

s.active_end_time,

s.date_created,

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' 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,

schedule_uid,

originating_server_id,

[name],

owner_sid,

enabled,

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

regards Matthias