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
Pingback: Job Name Function – Curated SQL
I tend to create a separate utility database for all these kinds of procedures rather than putting them in the system databases.