Get Job_ID Function in MSDB

At zillow we run over 1000 dataflow tasks through out job server. For the DBA group this means spending a good percentage of our time living in the msdb database, particularly in the sysjob tables. When we need to query for particular history of a job, or looking for particular job step, it requires an inner join to the sysjobs table. When you do this over and over it becomes cumbersome. To make our life a bit easier, we've written the following function that returns a job_id when a job name is passed in. I would think MS would have a canned version of this, but couldn't find one, but that just might be my google skills.

Function Definition

Use Msdb

go

create function dbo.GetJobID (@JobName varchar(500))

returns nchar(36)

with execute as caller

AS

Begin

Declare @Job_id nchar(36)

select @Job_id = job_id from msdb.dbo.sysjobs

where name = @JobName

Return(@Job_id)

End

Example of Use

select * from sysjobhistory where job_id = dbo.getjobid('test')

order by instance_id desc