SQL Server Agent > Jobs 1

[grnzbra]

In Enterprise Manager, selecting Management>SQL Server Agent>Jobs results in a list of all jobs and their status and other useful information.

In what table(s)/viws/stored procedures can this info be found?

 

[Mich]

msdb.sysjobs_view

A little script I use often.

select sjv.name as Name,  max(sjh.run_date) as LastExecution, 
            sjh.run_status as ExitCode from sysjobhistory sjh 
join sysjobs_view sjv
on sjh.job_id = sjv.job_id
group by sjv.name, sjh.run_status

-If it ain't broke, break it and make it better.

 

[grnzbra]

Thank you. That was a big help. I needed to take out the Exit Code because I would get the last date for each exit code in the table instead of just the last date the job was run. What I have now is:

select j.Name, c.name as Category, j.enabled, cast(h.run_status as char(1)) + ' (' + convert(char(10),h.run_date, 101) + ' ' + convert(char(11),h.run_time,14) + ')' as LastRunStatus
from
(select top 100 percent sjv.job_id, sjv.name as Name, sjv.category_id, max(sjh.run_date) as LastExecution
from sysjobhistory sjh
join sysjobs_view sjv
on sjh.job_id = sjv.job_id
group by sjv.job_id, sjv.name, sjv.category_id
ORDER BY sjv.name) as LJ
JOIN sysjobs j
ON LJ.job_id = j.job_id
JOIN syscategories c
ON LJ.category_id = c.category_id
JOIN sysjobhistory h
ON LJ.job_id = h.job_id and
LJ.LastExecution = h.run_date--ON LJ.job_id = j.job_id
WHERE h.step_name = '(Job outcome)'
ORDER BY j.Name