vwJob

This view is helpful for showing all the jobs that exist on this instance of SQL Server.  Note the very handy first column, NextRun which indicates which scheduled jobs are to be run in the future, or which have already run for this day.

If I ever need to shut down a server, or shut down SQL Agent, I query this view first so I know what jobs are scheduled to run next, or what jobs may not run during the downtime.

This view has a dependency on this object which is easy to install:
        udfFormatTimeWithColons

Use Admin

go

IF object_id('dbo.vwJob') Is Not Null

      DROP VIEW dbo.vwJob

go

CREATE VIEW [dbo].[vwJob]

AS

/*    DATE        AUTHOR            REMARKS

      9/3/11            PPaiva            Initial creation.

     

      DESCRIPTION

            Returns active jobs by next scheduled date (works only on SQL 2005).

      NOTE that since this JOINs to sysJobSchedules, this view

            does not necessarliy show a distinct list of Job Name.

      DEPENDENCIES

            udfFormatTimeWithColons()

      USAGE

            -- The next job to be executed is shown at the top

            SELECT *

            FROM vwJob

            ORDER BY Server, JobEn desc, SchedEn desc, NextRunDateTime

            SELECT *

            FROM vwJob

            ORDER BY Server, JobEn desc, SchedEn desc, OrderByMe, NextRunDateTime

*/

WITH cte (Server, JobEn, SchedEn, NextRunDateTime, Name,

                  Category, SchedName, Next_Run_Date, Next_Run_Time,

                  JobCreated, JobModified, Description, Job_ID)

AS

(

SELECT  Convert(varchar(50), ServerProperty('ServerName')) Server,

            J.Enabled JobEn,

            ss.Enabled SchedEn,

            Convert(varchar(16),

                  CASE WHEN IsNull(JS.Next_Run_Date, 0) = 0 THEN Null

                         ELSE Convert(datetime,

                                          Convert(varchar, JS.Next_Run_Date) +

                                          ' ' + dbo.udfFormatTimeWithColons(Right('000000' + Convert(varchar, JS.Next_Run_Time), 6))

                                                      )

                        END, 120) NextRunDateTime,

            J.Name,

            C.Name Category,

            ss.Name SchedName,

--          Originating_Server OrigServer,

            JS.Next_Run_Date,

            JS.Next_Run_Time,

            J.Date_Created JobCreated,

            J.Date_Modified JobModified,

            J.Description,   

            J.Job_ID

FROM msdb.dbo.sysJobs J

LEFT JOIN msdb.dbo.sysJobSchedules JS

      ON JS.Job_ID = J.Job_ID

LEFT JOIN msdb.dbo.syscategories C

      ON C.Category_ID = J.Category_ID

LEFT JOIN msdb.dbo.sysschedules ss

      ON ss.schedule_ID = js.schedule_ID

)

SELECT CASE WHEN NextRunDateTime Is Null

                        THEN 'Null'

                   WHEN NextRunDateTime < GetDate() - 1

                        THEN 'Past'

                  ELSE 'Future'

            END NextRun,

            *,

            CASE WHEN NextRunDateTime Is Null

                        THEN '2200-01-01'

                   WHEN NextRunDateTime < GetDate() - 1

                        THEN '2100-01-01'

                  ELSE NextRunDateTime

            END OrderByMe

FROM cte