Here's a version that works on case sensitive servers. I've also refformated with SQL Refactor.
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 int
, Next_Run_Date int
, Next_Run_Time int
, 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
SELECT
j.name as JobName
,
/*****************************************/ 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
, CASE when len(lr.last_run_time) >= 5
and len(lr.last_run_time) <= 6
then substring(cast(lr.last_run_time as varchar(10)) , 1 ,
( case when len(lr.last_run_time) = 5 then 1
else 2
end )) + '-'
+ substring(cast(lr.last_run_time as varchar(10)) ,
( case when len(lr.last_run_time) = 5 then 2
else 3
end ) , 2) + '-'
+ substring(cast(lr.last_run_time as varchar(10)) ,
( case when len(lr.last_run_time) = 5 then 4
else 5
end ) , 2)
when len(lr.last_run_time) = 4
then '12:'
+ ( CASE WHEN LEN(substring(cast(lr.last_run_time as varchar(10)) ,
1 ,
( case when len(lr.last_run_time) = 3
then 1
else 2
end ))) > 1
THEN substring(cast(lr.last_run_time as varchar(10)) ,
1 ,
( case when len(lr.last_run_time) = 3
then 1
else 2
end ))
ELSE '0'
+ substring(cast(lr.last_run_time as varchar(10)) ,
1 ,
( case when len(lr.last_run_time) = 3
then 1
else 2
end ))
END ) + ':'
+ ( CASE WHEN LEN(substring(cast(lr.last_run_time as varchar(10)) ,
( case when len(lr.last_run_time) = 3
then 2
else 3
end ) , len(lr.last_run_time))) > 1
THEN substring(cast(lr.last_run_time as varchar(10)) ,
( case when len(lr.last_run_time) = 3
then 2
else 3
end ) , len(lr.last_run_time))
ELSE '0'
+ substring(cast(lr.last_run_time as varchar(10)) ,
( case when len(lr.last_run_time) = 3
then 2
else 3
end ) , len(lr.last_run_time))
END )
else '12:00:00'
end 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 'Never Ran'
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 LEN(p.Next_Run_Time) > 5
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)
WHEN LEN(p.Next_Run_Time) <= 5
and LEN(p.Next_Run_Time) > 1
THEN '0' + substring(cast(p.Next_Run_Time as varchar(100)) , 1 , 1)
+ '-' + substring(cast(p.Next_Run_Time as varchar(100)) , 2 , 2)
+ '-' + substring(cast(p.Next_Run_Time as varchar(100)) , 4 , 2)
ELSE '12:00:00'
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 , '--')
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
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