LearnItFirst User Forum

CREATE PROC dbo.GetJobsScheduledInTheNextNHours (@HowManyHoursAhead INT = 24)
AS
/*
    Author: Scott Whigham from http://www.LearnItFirst.com/

    Description: This script returns all jobs scheduled to run in the next "N" hours

    Misc Notes: Special thanks to whoever runs http://www.prophecie.co.uk/Default.aspx?dc=200512 as that's where I got the idea for this script!
   
    Versions: SQL Server 2005
   
    Creation Date: May 9, 2007

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
WITH OurJobs AS (
    SELECT  job.job_id,  job.[name]
      , CASE job.[description] WHEN 'No description available.' THEN NULL ELSE job.description END AS Description
      , job.date_modified
      , CASE sched.next_run_date
            WHEN 0 THEN 'Never'
            ELSE
              CONVERT(varchar(10), CONVERT(smalldatetime, CAST(sched.next_run_date as varchar), 120), 120)+' '+
              RIGHT('0'+CAST((sched.next_run_time/10000) AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((sched.next_run_time-((sched.next_run_time/10000)*10000))/100 AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((sched.next_run_time-((sched.next_run_time/10000)*10000)-((sched.next_run_time-((sched.next_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
      END AS NextRunDateTime
      , (
        SELECT CASE last_run_date
            WHEN 0 THEN 'Never'
            ELSE
              CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+' '+
              RIGHT('0'+CAST((last_run_time/10000) AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+':'+
              RIGHT('0'+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
          END AS LastRunDateTime
        FROM msdb.dbo.sysjobsteps
        WHERE job_id = job.job_id AND step_id = (
          SELECT MAX(step_id)
          FROM msdb.dbo.sysjobsteps
          WHERE job_id = job.job_id
        )
      ) as LastSuccessfulExecution
    FROM msdb.dbo.sysjobs job JOIN msdb.dbo.sysjobschedules sched
        ON sched.job_id = job.job_id
    WHERE job.enabled = 1 -- remove this if you wish to return all jobs
        AND sched.next_run_date > 0
)
SELECT * FROM OurJobs
WHERE DATEDIFF(hh, GETDATE(), NextRunDateTime) <= @HowManyHoursAhead
GO
EXEC GetJobsScheduledInTheNextNHours 24