Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Mike Pearon List All Jobs and Their Schedules RE: List All Jobs and Their Schedules

  • November 20, 2017 at 6:52 pm

    #1968923

    camaro305hp - Monday, November 20, 2017 5:02 PM

    Jeff Moden - Monday, November 20, 2017 4:23 PM

    camaro305hp - Monday, November 20, 2017 3:57 PM

    rwest 44523 - Wednesday, July 19, 2017 8:04 AM

    The best of all worlds:

    use [msdb]

    Declare @weekDay Table
    (
      mask int
    , maskValue varchar(32)
    );
    ................................................................

    Thank you!  This is exactly what I've been looking for.  Extremely useful script.

    Since it doesn't tell you of any failures, cancellations, nor how many times something has executed in, say, the last 24 hours, what will you do with the information that it produces?

    For my purposes of using this script, I'm not interested in those things (I am interested in them, but that's not what I'm using this script for).  I'm using it to do a daily backup of all my jobs/schedules.  In case of catastrophic failure, I can have a quick list of all the jobs/steps/schedules that I can use to quickly get the jobs back up without messing around with the msdb backup.  I do the same thing with logins and linked servers.

    Got it.  Thanks for the feedback.

    You do know that you can script out all of the jobs, correct? 
    1.  In the Explorer Window of SSMS (press the {f8} key to get there if you don't know how), scroll down to the "SQL Agent Agent", expand it, the single click on JOBS. 
    2. Once that's done, press the {f7} key to open the Explorer Detail window.  There, all of the jobs will be listed. 
    3. Select all of the jobs at once and then right click on the selection.  Select "Script Job As" and then follow your nose.  All the selected jobs will be scripted to the destination you chose and that includes the schedules and everything else.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)