• 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