Utility function: dbo.JobName(@job_id)

As often as I have to pull a job’s name based on its job_id in the MSDB system tables, I’m truly stunned that I’ve never done this before:

CREATE FUNCTION dbo.JobName
(
@job_id UNIQUEIDENTIFIER
)
/* A little utility function to return a job name based on the job_id.
No warranty or guarantee implied. Use at your own risk. And don’t take wooden nickles.
http://www.MidnightDBA.com/Jen
*/
RETURNS sysname
AS
BEGIN
DECLARE @name sysname;
SELECT @name = name
FROM msdb..sysjobs
WHERE job_id = @job_id;
RETURN @name;
END;
GO

So yeah, now it takes fewer keystrokes to get the job name. I used to:

SELECT *
FROM msdb..sysjobschedules
WHERE job_id IN ( SELECT job_id
FROM msdb..sysjobs
WHERE name LIKE ‘CollectorDBFilePropertiesGet-%’ );

But now I:

SELECT *
FROM msdb..sysjobschedules
WHERE dbo.JobName(job_id) LIKE ‘CollectorDBFilePropertiesGet-%’ ;

Good lord. I guess it’s just a longstanding superstition about putting user objects in system databases. But enough is enough!

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen