How Get SQL Server Job Name with help of program name

SQLAgent – TSQL JobStep (Job 0x0854F6E4F9E5AE48B164966C0553C8B0 : Step 4)

Step -1 Create Below function

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[GetJobIdFromProgramName] (

@program_name nvarchar(128)

)

RETURNS uniqueidentifier

AS

BEGIN

DECLARE @start_of_job_id int

SET @start_of_job_id = CHARINDEX(‘(Job 0x’, @program_name) + 7

RETURN CASE WHEN @start_of_job_id > 0 THEN CAST(

SUBSTRING(@program_name, @start_of_job_id + 06, 2) + SUBSTRING(@program_name, @start_of_job_id + 04, 2) +

SUBSTRING(@program_name, @start_of_job_id + 02, 2) + SUBSTRING(@program_name, @start_of_job_id + 00, 2) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 10, 2) + SUBSTRING(@program_name, @start_of_job_id + 08, 2) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 14, 2) + SUBSTRING(@program_name, @start_of_job_id + 12, 2) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 16, 4) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 20,12) AS uniqueidentifier)

ELSE NULL

END

END

Step – 2

Execute below query –

SELECT *

FROM msdb.dbo.sysjobs

WHERE

job_id = dbo.GetJobIdFromProgramName (‘SQLAgent – TSQL JobStep (Job 0x0854F6E4F9E5AE48B164966C0553C8B0 : Step 4) ‘)