To get the Job status information, we need to use the relevant information from two job related tables namely sysjobs and sysjobhistory.
The run_status column of sysjobhistory has the current status of the job.
You can refer to the following script:
select distinct j.Name as "Job Name",h.run_date as LastStatusDate,
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'Executing'
end as JobStatus
from msdb.dbo.sysJobHistory h, msdb.dbo.sysJobs j
where j.job_id = h.job_id and h.run_date =
(select max(hi.run_date) from msdb.dbo.sysJobHistory hi where h.job_id = hi.job_id)
Hope this will help you...