How to get SQL Server scheduled Job status from system table.

In the SQL Server ajent’s job execution history stored in the msdb system database.

from the below query you can get the JobID, JobName, LastRunDateTime, LastRunStatus, LastRunStatusMessage and NextRunDate from the back end.

SELECT     [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , CASE
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                 CAST([sJOBH].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                         , 3, 0, ':')
                    , 6, 0, ':')
                 AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                 , 3, 0, ':')
            , 6, 0, ':')
        AS [LastRunDuration (HH:MM:SS)]
    , [sJOBH].[message] AS [LastRunStatusMessage]
    , CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' '
                 + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                         , 3, 0, ':')
                    , 6, 0, ':')
                 AS DATETIME)
      END AS [NextRunDateTime],
      convert(datetime,convert(date,current_timestamp))  AS 'LOADED_DATE'
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]
    LEFT JOIN (
                 SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
             ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT
                     [job_id]
                    , [run_date]
                     , [run_time]
                    , [run_status]
                     , [run_duration]
                    , [message]
                     , ROW_NUMBER() OVER (
                                             PARTITION BY [job_id]
                                             ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory]
                WHERE [step_id] = 0
             ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
ORDER BY [JobName]

Thanks you !!!!