How to find which SQL Server job is using the stored procedure?

Question

We have system tables in SQL Server such as sysjobs, syscomments, sysobjects.

I need to find list of SQL Server jobs running in SQL Server agent using a specific stored procedure.

Such like ..

select * 
from sysobjects 
where name = 'sp_mystoredprocedure'

I need to find which SQL Server jobs are using the stored procedure which I am passing as an input parameter.

Answer 1

You need to query the table sysjobsteps which is found in the msdb database. Though there is nothing which gives you the exact stored procedure, but you can search with keywords.

SELECT  j.job_id,
        s.srvname,
        j.name,
        js.step_id,
        js.command,
        j.enabled 
FROM    msdb.dbo.sysjobs j
JOIN    msdb.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%'

References:
https://gist.github.com/fcmendoza/1878088

Answer 2

Source: https://stackoverflow.com/a/11828818/3585278

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%'
);