To Find List of Jobs, Last Run Status and Schedules

To find out lsit of jobs and thier details.

SELECT
J.Name
,CASE WHEN J.Enabled=1 THEN 'Enabled' ELSE 'Disabled' END AS JobEnableStatus
,SJS.Command AS Query
,SJS.Step_Name
,LastRunDate
,LastRunTime
,CASE WHEN LastRunStatus =1 THEN 'Success' ELSE 'Failed' END AS LastRunStatus
,Next_Run_Date
,CASE len(Next_Run_Time)
WHEN 3 THEN CAST('00:0'+ LEFT(RIGHT(Next_Run_Time,3),1)+':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
WHEN 4 THEN CAST('00:'+ LEFT(RIGHT(Next_Run_Time,4),2) +':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
WHEN 5 THEN CAST('0' + LEFT(RIGHT(Next_Run_Time,5),1) +':' + LEFT(RIGHT(Next_Run_Time,4),2) +':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
WHEN 6 THEN CAST(LEFT(RIGHT(Next_Run_Time,6),2) +':' + LEFT(RIGHT(Next_Run_Time,4),2) +':' + RIGHT(Next_Run_Time,2) AS CHAR (8))
END AS NextRunTime
,SS.Name AS SchduleFrequency
,CASE LEN(Q1.Run_Duration)
WHEN 1 THEN cast('00:00:0'
+ cast(Q1.Run_Duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q1.Run_Duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q1.Run_Duration,3),1)
+':' + right(Q1.Run_Duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q1.Run_Duration,4),2)
+':' + right(Q1.Run_Duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q1.Run_Duration,5),1)
+':' + Left(right(Q1.Run_Duration,4),2)
+':' + right(Q1.Run_Duration,2) as char (8))
WHEN 6 THEN cast(Left(right(Q1.Run_Duration,6),2)
+':' + Left(right(Q1.Run_Duration,4),2)
+':' + right(Q1.Run_Duration,2) as char (8))
END as 'Job Ran Duration'
FROM MSDB..SYSJobs J
INNER JOIN MSDB..SysJobSchedules S ON J.job_id= S.job_id
LEFT JOIN MSDB..SYSJobSteps SJS ON SJS.job_id = S.job_id
LEFT JOIN MSDB..SysSchedules SS ON SS.Schedule_Id = S.Schedule_Id
LEFT JOIN (
SELECT
job_id
,MAX(run_duration) AS run_duration
,MAX(Run_Status) AS LastRunStatus
,MAX(Run_Date) AS LastRunDate
,CASE LEN(MAX(Run_Time))
WHEN 3 THEN CAST('00:0'
+ LEFT(RIGHT(MAX(Run_Time),3),1)
+':' + RIGHT(MAX(Run_Time),2) as char (8))
WHEN 4 THEN CAST('00:'
+ LEFT(RIGHT(MAX(Run_Time),4),2)
+':' + RIGHT(MAX(Run_Time),2) as char (8))
WHEN 5 THEN CAST('0' + LEFT(RIGHT(MAX(Run_Time),5),1)
+':' + LEFT(RIGHT(MAX(Run_Time),4),2)
+':' + RIGHT(MAX(Run_Time),2) as char (8))
WHEN 6 THEN CAST(LEFT(RIGHT(MAX(Run_Time),6),2)
+':' + LEFT(RIGHT(MAX(Run_Time),4),2)
+':' + RIGHT(MAX(Run_Time),2) AS CHAR (8))
END AS LastRunTime


FROM MSDB..SysJobHistory
GROUP BY job_id
) Q1
ON J.job_id = Q1.job_id

WHERE Category_Id =0 AND J.Enabled=1

ORDER BY LastRunDate,LastRunTime,J.Name


Thanks,