"Zekske" <Zek***@discussions.microsoft.com> wrote in message
news:1DF90117-DAD7-494D-81A8-363C715A743F@microsoft.com...
>I found in BOL the meaning of the values but is there a script I can use to
> get an overview of the scheduling information for all my jobs with a
> result
> everyone can understand (f.e every sunday at 8am?)
> Like you can see in the management studio?
>
> Regards

"Uri Dimant" wrote:

> Hi
> This script writen by Neil works very well on SQL Server 2000. For SQL
> Server 2005 , see the ouptut as
> sp_helptext 'sp_help_jobschedule' and modify  or run it for your needs
>
>
>
>
> /*** 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
>
> "Zekske" <Zek***@discussions.microsoft.com> wrote in message
> news:1DF90117-DAD7-494D-81A8-363C715A743F@microsoft.com...
> >I found in BOL the meaning of the values but is there a script I can use to
> > get an overview of the scheduling information for all my jobs with a
> > result
> > everyone can understand (f.e every sunday at 8am?)
> > Like you can see in the management studio?
> >
> > Regards
>
>
>