Creating a list of SQL Server Agent Job Status in RealTime

Essentially I want to replicate the functionality of the SQL Agent Job Monitor.

I am trying to find the correct system view or stored procedure to get a list of SQL Agent Jobs that are executing. Similar to what you would see when you use the SQL Agent Job Monitor to oversee SQL Agent Job execution. (I have verified that I have sysadmin permissions.)

There is a stored procedure called msdb.dbo.sp_help_jobactivity however it still does not return information on currently running jobs. There is a system table called 'sysjobactivity' that contains job information but not the runtime status of jobs. In the BOL there is a detailed list of all of the tables that SQL Server Agent uses...but none of them seem to give runtime information.

Do I need to join the sysjobactivity table to some other process table to get realtime status?

...cordell...

This should get you started:
http://www.microsoft.com/technet/abouttn/flash/tips/tips_060804.mspx

...except that the author is wrong. You can't run sp_help_job into a table because it calls a nested stored proc.

This, however, should actually work:

declare @.job_owner_name varchar(100)
set @.job_owner_name = '' -- insert job owner name here, required for extended stored proc

if OBJECT_ID('tempdb..#xp_results') is not null
drop table #xp_results

CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

insert into #xp_results
exec master.dbo.xp_sqlagent_enum_jobs 1, @.job_owner_name

select name, * from #xp_results rj
inner join msdb.dbo.sysjobs sj
on sj.job_id = rj.job_id
where rj.running = 1