Get current running Job Schedule Id or Name in SQL Server

Here is a simple TSQL code that gives you the Schedule Name & ID which invoked he current job execution. 



DECLARE @Schedule_Name_ID nVARCHAR(1000)
DECLARE @Job_ID nVARCHAR(100)
DECLARE @State INT
DECLARE @Job_Name nVARCHAR(1000)

SET @Job_Name = 'Your Job Name Goes Here'

SELECT @Job_ID = job_id
FROM msdb.dbo.SysJobs
WHERE name = @Job_Name

IF(@Job_ID IS NOT NULL)
BEGIN


CREATE TABLE #job_current_state
(
 Job_ID UNIQUEIDENTIFIER,
 Last_Run_Date INT,
 Last_Run_Time INT,
 Next_Run_Date INT,
 Next_Run_Time INT,
 Next_Run_Schedule_ID INT,
 Requested_To_Run INT,
 Request_Source INT,
 Request_Source_ID VARCHAR(100),
 Running INT,
 Current_Step INT,
 Current_Retry_Attempt INT,
 State INT
)
INSERT  INTO #job_current_state
EXEC master.dbo.xp_sqlagent_enum_jobs 1, garbage
 
SELECT  @Schedule_Name_ID = Request_Source_ID ,@State = State
FROM    #job_current_state
where job_id = @Job_ID

SELECT ISNULL(@Schedule_Name_ID,'NA') Schedule_Name_ID
,CASE @State WHEN 1 THEN 'Running' ELSE 'Stopped' END State

DROP TABLE #job_current_state

END

ELSE

BEGIN

SELECT 'Job does not exist' output

END