CODEWITHSUNDEEP
sql-server-2008-r2sql-agent
Jeff B
Jeff B

Reputation: 545

What SQL Server agent job is running a stored procedure?

I have a stored procedure that I know is being run somewhere in SQL Agent I just don't know where. Is there an easy way to find out? Easier than to script all of the jobs and search for it there ?

TIA - Jeff.

Upvotes: 0

Views: 235

Answers (1)

Pred
Pred

Reputation: 9042

You can use the msdb.dbo.sysjobsteps table. The command field contains the SQL statement for the step.

Here is a sample query to do it:

USE [msdb]
GO
SELECT
    J.job_id,
    J.name,
    JS.step_id,
    JS.command,
    J.enabled 
FROM
    dbo.sysjobs J
    INNER JOIN dbo.sysjobsteps JS
        ON JS.job_id = J.job_id 
WHERE
    JS.command LIKE N'%string to find%'

Please note, that the commands are different for every subsystem in the SQL Agent. You can find SP names in T-SQL steps, but you can not find them in SSIS package or PowerShell or other types of steps.

Here is the documentation for the sysjobsteps table: http://msdn.microsoft.com/en-us/library/ms187387(v=sql.105).aspx

Upvotes: 1

Related Questions