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,

case h.run_status

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...