Post reply

get sql agent job name and it's schedule

  • December 4, 2013 at 1:07 pm

    #301194

    How to get just sql agent job name and it's schedule?

  • December 4, 2013 at 1:24 pm

    #1670840

    Probably the easiest way is to pull up the properties of the Job and check there. If you want to grab it via T-SQL, I've been using the code below. It's not perfect, as I set it up to only check schedules I know I've got, so odd schedules will be missed, but shouldn't be too hard to add other possible day combinations.

    Plus, I'm sure some of the old hands will find plenty of things I could've done better, or differently..

    Suggestions appreciated!

    😎

    use [msdb];

    select

    sj.name as 'Job Name', sj.enabled as 'Job Enabled'

    , suser_sname(sj.owner_sid) as 'Job Owner' /*sj.description,*/

    , sched.schedule_id as 'Schedule ID'

    , sched1.name as 'Schedule Name'

    , sched1.enabled as 'Sched Enabled'

    , CASE sched1.freq_type

    When 1 then 'One Time '/* + cast(sched1.freq_type as varchar(2))*/

    When 4 then 'Daily '/* + cast(sched1.freq_type as varchar(2))*/

    When 8 then 'Weekly '/* + cast(sched1.freq_type as varchar(2))*/

    When 16 then 'Monthly '/* + cast(sched1.freq_type as varchar(2))*/

    When 32 then 'Monthly '/* + cast(sched1.freq_type as varchar(2))*/

    end as Freq_type

    , Case sched1.freq_type

    when 0 then 'Unused'

    when 1 then 'Unused'

    When 4 then 'Every ' + cast(sched1.freq_interval as varchar(2)) + ' days'

    when 8 then

    case sched1.freq_interval

    when 1 then 'Sun'

    when 2 then 'Mon'

    when 4 then 'Tue'

    when 8 then 'Wed'

    when 9 then 'SuW'

    when 16 then 'Thu'

    when 32 then 'Fri'

    when 64 then 'Sat'

    when 62 then 'MTWThF'

    when 63 then 'SuMTWThF'

    when 124 then 'TWThFSa'

    end

    When 16 then 'On the ' + cast(sched1.freq_interval as varchar(2)) + 'th DOM'

    End as Freq_Interval

    , stuff(stuff(right('000000' + rtrim(cast(sched1.active_start_time as varchar(6))), 6), 3, 0, ':'), 6, 0, ':') as 'Start Time'

    from sysjobs as sj

    full join sysjobschedules as sched

    on sj.job_id = sched.job_id

    full join sysschedules as sched1

    on sched.schedule_id = sched1.schedule_id

    where sj.name is not null

    --and suser_sname(sj.owner_sid) like '%mensing%'

    order by sj.owner_sid, sched1.freq_type, sched1.freq_interval, sched1.active_start_time

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply