Get Scheduled Jobs from Multiple Servers

I needed to be able to loop thru several SQL servers and get the active maintenance jobs and the next run times. I hobled together several different scripts and came up with the below list.


-- Use the to find "Maintenance" Jobs. Just put a "%" in it for all jobs. % is a wild card in TSQL
DECLARE @JobName VARCHAR(50)
SET @JobName = 'maintenance%'

-- Server List, ALLWAYS have a comma at end
-- This list MUST be linked servers from whatever SQL server you are running it on
DECLARE @ServerName VARCHAR(30) 
       ,@position INT 
       ,@ServerList varchar(8000),
       @HoursForward varchar(300); 
SET @position=0; 
SET @ServerList='SAMY-PC\SQL2008R2'; 

-- Variable to hold dynamic SQL
DECLARE @sql VARCHAR(8000)     

-- Beginning of loop for servers
WHILE charindex(',',@ServerList)>0 
BEGIN 

-- Get the next server in the list
SET @ServerName = cast(substring(@ServerList,0, charindex(',',@ServerList)) as VARCHAR(30)) 

-- This gets all the jobs, part 2 below queries the results
SET @sql = 'WITH OurJobs AS (
    SELECT  job.[name]
     , CASE job.[description] WHEN ''No description available.'' THEN NULL ELSE job.description END AS Description
     , 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 ' + @ServerName + '.msdb.dbo.sysjobsteps
        WHERE job_id = job.job_id AND step_id = (
          SELECT MAX(step_id)
          FROM ' + @ServerName + '.msdb.dbo.sysjobsteps
          WHERE job_id = job.job_id
        )
      ) as LastSuccessfulExecution
      , job.date_modified
   FROM ' + @ServerName + '.msdb.dbo.sysjobs job JOIN ' + @ServerName + '.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 --GETDATE()
)

-- Part 2, queries table of jobs
SELECT * FROM OurJobs 
WHERE DATEDIFF(hh, GETDATE(), NextRunDateTime) <= ' + CAST(@HoursForward AS CHAR(3)) + '
AND NextRunDateTime > GETDATE()
AND name LIKE ''' + @JobName + '''
ORDER BY NextRunDateTime ASC'

--PRINT @sql
EXEC (@sql)

SET @ServerList = substring(@ServerList, charindex(',',@ServerList)+1, LEN(@ServerList) - @position);

END