— Find Jobname which is Running a Procedure in sql server

— Find SQL Job name based on Procedure/trigger/function name

SELECT j.name
FROM msdb.dbo.sysjobs AS j
WHERE EXISTS
(
SELECT 1 FROM msdb.dbo.sysjobsteps AS s
WHERE s.job_id = j.job_id
AND s.command LIKE ‘%procedurename%’
);

———– SQL 2005/2008——————–

USE [msdb]
GO
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE N’%KEYWORD_SEARCH%’
GO

———in SQL 2000———–
USE [msdb]
GO
SELECT j.job_id,
j.originating_server,
j.name,
js.step_id,
js.command,
j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
WHERE js.command LIKE N’%KEYWORD_SEARCH%’
GO

Advertisements