Here's a version with the times format correct with jobs just barely past midnight
IF OBJECT_ID('tempdb..#enum_job') > 0
DROP TABLE #enum_job
DECLARE
@MxCnt INT
, @Cnt INT
DECLARE @JobID VARBINARY(MAX)
DECLARE @RunnableJobs INT
DECLARE @Owner VARCHAR(20)
/****Pass the parameters for viewing the job lists***********************************/
SET @RunnableJobs = NULL
--> 1: All runnable jobs
--> 0: All Non Runnable jobs
--> Null: All enabled jobs
/************************************************************************************/
SET @Owner = SYSTEM_USER
CREATE TABLE #enum_job
(
RowID INT IDENTITY(1 , 1)
, Job_ID UNIQUEIDENTIFIER
, Last_Run_Date INT
, Last_Run_Time VARCHAR(6)
, Next_Run_Date INT
, Next_Run_Time VARCHAR(6)
, Next_Run_Schedule_ID INT
, Requested_To_Run INT
, Request_Source INT
, Request_Source_ID VARCHAR(100)
, Running INT
, Current_Step INT
, Current_Retry_Attempt INT
, State INT
, JobID_var VARCHAR(MAX)
)
INSERT INTO
#enum_job
(
Job_ID
, Last_Run_Date
, Last_Run_Time
, Next_Run_Date
, Next_Run_Time
, Next_Run_Schedule_ID
, Requested_To_Run
, Request_Source
, Request_Source_ID
, Running
, Current_Step
, Current_Retry_Attempt
, State
)
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1 , @Owner
--<1 -para -->sysadmin> --<2 -para -->owner>????
SET @MxCnt = @@identity
SET @Cnt = 1
WHILE ( @Cnt <= @MxCnt )
BEGIN
SET @JobID = (
SELECT
Job_ID
FROM
#enum_job
WHERE
RowID = @Cnt
)
PRINT @JobID
UPDATE
#enum_job
SET
JobID_var = (
SELECT
CAST('' AS XML).value('xs:hexBinary(sql:variable("@JobID") )' ,
'varchar(max)')
)
WHERE
RowID = @Cnt
SET @Cnt = @Cnt + 1
END
UPDATE
#enum_job
SET
Next_Run_Time = RIGHT('000000' + Next_Run_Time , 6)
SELECT
j.name AS JobName
, j.description AS JobDescription
, c.[name] AS JobCategoryName
,
/*****************************************/ CASE WHEN lr.last_run_date = 0
THEN 'Never Ran'
ELSE CONVERT(VARCHAR(100) , CONVERT(DATETIME , SUBSTRING(CAST(lr.last_run_date AS VARCHAR(100)) , 1 , 4)
+ '-'
+ SUBSTRING(CAST(lr.last_run_date AS VARCHAR(100)) , 5 , 2)
+ '-'
+ SUBSTRING(CAST(lr.last_run_date AS VARCHAR(100)) , 7 , 8)) , 6)
END AS LastRunDate
, SUBSTRING(RIGHT('000000' + CAST(lr.last_run_time AS VARCHAR(100)) , 6) , 1 ,
2) + ':' + SUBSTRING(RIGHT('000000'
+ CAST(lr.last_run_time AS VARCHAR(100)) ,
6) , 3 , 2) + ':'
+ SUBSTRING(RIGHT('000000' + CAST(lr.last_run_time AS VARCHAR(100)) , 6) ,
5 , 2) AS LastRunTime
, CASE WHEN lr.last_run_outcome = 1 THEN 'Success'
WHEN lr.last_run_outcome = 0 THEN 'Failed'
ELSE 'Cancel'
END AS LastRunStatus
, CASE WHEN LEN(lr.last_run_duration) <= 2
THEN '00 hrs : 00 min : ' + ' '
+ CAST(lr.last_run_duration AS VARCHAR(100)) + 'sec'
WHEN LEN(lr.last_run_duration) > 2
AND LEN(lr.last_run_duration) <= 4
THEN '00 hrs : '
+ SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) , 1 ,
( LEN(lr.last_run_duration) - 2 )) + ' min : ' + ' '
+ SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) ,
( LEN(lr.last_run_duration) - 1 ) ,
LEN(lr.last_run_duration)) + 'sec'
WHEN LEN(lr.last_run_duration) >= 5
AND LEN(last_run_duration) <= 6
THEN SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) , 1 ,
( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 1
ELSE 2
END )) + ' hrs :'
+ SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) ,
( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 2
ELSE 3
END ) ,
( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 2
ELSE 2
END )) + ' min : '
+ SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(100)) ,
( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 4
ELSE 5
END ) ,
( CASE WHEN LEN(lr.last_run_duration) = 5 THEN 2
ELSE 2
END )) + ' sec : '
ELSE CAST(SUBSTRING(CAST(lr.last_run_duration AS VARCHAR(10)) , 1 , 3)
/ 24 AS VARCHAR(10)) + '' + 'days'
END AS 'LastRunDuration'
,
/*****************************************/ CASE WHEN p.Next_Run_Date = 0
THEN 'Not scheduled'
ELSE CONVERT(VARCHAR(100) , CONVERT(DATETIME , SUBSTRING(CAST(p.Next_Run_Date AS VARCHAR(100)) , 1 , 4)
+ '-'
+ SUBSTRING(CAST(p.Next_Run_Date AS VARCHAR(100)) , 5 , 2)
+ '-'
+ SUBSTRING(CAST(p.Next_Run_Date AS VARCHAR(100)) , 7 , 8)) , 6)
END AS NextRunDate
, CASE WHEN p.Next_Run_Date <> 0
THEN SUBSTRING(CAST(p.Next_Run_Time AS VARCHAR(100)) , 1 , 2) + ':'
+ SUBSTRING(CAST(p.Next_Run_Time AS VARCHAR(100)) , 3 , 2) + ':'
+ SUBSTRING(CAST(p.Next_Run_Time AS VARCHAR(100)) , 5 , 2)
ELSE ''
END AS NextRunTime
,
/*****************************************/ ISNULL(DB_NAME(s.dbid) , '') DatabaseName
, CASE WHEN p.Running = 1 THEN 'Running'
ELSE 'Idle'
END AS Current_Job_Status
, CASE WHEN Current_Step > 0
THEN 'Step:' + ' ' + CAST(Current_Step AS VARCHAR(10))
+ ' execution under process.'
ELSE 'Idle'
END AS Current_Step_Execution
, ISNULL(st.text , '--') AS CurrentQueryString
, ISNULL(s.Spid , '') Spid
, 'IsBlocked' = CASE WHEN s.blocked <> 0
THEN 'Blocked By SPID: ' + ''
+ CAST(s.blocked AS VARCHAR(10))
ELSE '0'
END
, ISNULL(CPU , 0) CPU
, ISNULL(Physical_IO , 0) Physical_IO
, ISNULL(Memusage , 0) Memusage
, 'spid status' = ISNULL(s.status , '--')
, 'WaitResourceType' = ISNULL(s.LastWaitType , '--')
, CONVERT(SMALLDATETIME, STUFF(STUFF(lr.last_run_date, 5, 0, '-'), 8, 0, '-')) AS dtLastRunDate
, CONVERT(SMALLDATETIME, STUFF(STUFF(p.Next_Run_Date, 5, 0, '-'), 8, 0, '-')) AS dtNextRunDate
FROM
#enum_job p
JOIN msdb.dbo.sysjobs j
ON j.job_id = p.Job_ID
LEFT JOIN master.sys.sysprocesses s
ON SUBSTRING(s.program_name , 32 , 32) = p.JobID_var
LEFT JOIN msdb.dbo.sysjobservers lr
ON lr.job_id = p.Job_ID
LEFT JOIN sys.dm_exec_requests dm
ON dm.session_id = s.spid
LEFT OUTER JOIN msdb.dbo.syscategories c
ON j.category_id = c.category_id
OUTER APPLY sys.dm_exec_sql_text(dm.sql_handle) AS st
WHERE
j.enabled = 1
AND p.Running = ISNULL(@RunnableJobs , p.Running)
ORDER BY
JobName
IF OBJECT_ID('tempdb..#enum_job') > 0
DROP TABLE #enum_job