Querying SQL Server Agent Job Information

/*Fetch SQL Server Agent Job Details

Created By: Adarsh Agrawal

Created Date: 04/06/2015

*/

CREATE TABLE #JobResults (Job_ID UNIQUEIDENTIFIER NOT NULL,

Last_Run_Date INT NOT NULL,

Last_Run_Time INT NOT NULL,

Next_Run_date INT NOT NULL,

Next_Run_Time INT NOT NULL,

Next_Run_Schedule_ID INT NOT NULL,

Requested_to_Run INT NOT NULL,

Request_Source INT NOT NULL,

Request_Source_id SYSNAME

COLLATE Database_Default NULL,

Running INT NOT NULL,

Current_Step INT NOT NULL,

Current_Retry_Attempt INT NOT NULL,

Job_State INT NOT NULL )

INSERT  #JobResults

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, '';

SELECT job.name AS [JobName],

(SELECT MAX(CAST( STUFF(STUFF(CAST(jh.run_date AS VARCHAR),7,0,'-'),5,0,'-') + ' ' +

STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') AS DATETIME))

FROM msdb.dbo.sysjobs AS j

INNER JOIN msdb.dbo.sysjobhistory AS jh

ON jh.job_id = j.job_id AND jh.step_id = 0 ) AS [LastCompletedDateTime],

(SELECT  TOP 1 start_execution_date

FROM msdb.dbo.sysjobactivity

WHERE job_id = r.job_id

ORDER BY start_execution_date DESC) AS [JobStartDateTime],

CASE

            WHEN r.running = 0

THEN

CASE

            WHEN jobInfo.lASt_run_outcome = 0 THEN 'Failed'

            WHEN jobInfo.lASt_run_outcome = 1 THEN 'Success'

            WHEN jobInfo.lASt_run_outcome = 3 THEN 'Canceled'

            ELSE 'Unknown'

END

            WHEN r.job_state = 0 THEN 'Success'

            WHEN r.job_state = 4 THEN 'Success'

            WHEN r.job_state = 5 THEN 'Success'

            WHEN r.job_state = 1 THEN 'In Progress'

            WHEN r.job_state = 2 THEN 'In Progress'

            WHEN r.job_state = 3 THEN 'In Progress'

            WHEN r.job_state = 7 THEN 'In Progress'

            ELSE 'Unknown'

END AS [RunStatusDescription]

FROM #JobResults AS r

LEFT OUTER JOIN msdb.dbo.sysjobservers AS jobInfo

ON r.job_id = jobInfo.job_id

INNER JOIN msdb.dbo.sysjobs AS job

ON r.job_id = job.job_id

WHERE job.[enabled] = 1

Order by JobName

Drop table #JobResults