Search SQL Server Job based on keyword provided

MonitoringSearch SQL Server Job based on keyword provided

We have hundreds of system as well as customize job setup in SQL Server Agent for our production databases. It is hard to find one particular job out of this long list. We tend to maintain document for each job we set but I always try to find solution right from the SSMS if it is a question of SQL Server.

It happens many time that I got SP name and have to find SQL Server Job(s) which is calling that particular stored procedure. It is not at all feasible for me to go through each and every SQL Server Job, open it’s step and find SP/Table is being called from it.

Following small SELECT,  based on SysJobs and SysJobSteps system view , script always come as a rescue in this kind of situation.

[sourcecode language=”sql”]

USE MSDB
GO

SELECT
JobStep.Database_Name,
SysJob.name AS Job_Name,
JobStep.command,
JobStep.step_id,
JobStep.Last_Run_Date,
JobStep.Last_Run_Time,
JobStep.Last_Run_Duration
FROM
sysjobs AS SysJob
INNER JOIN
sysjobsteps JobStep
ON
SysJob.job_id = JobStep.job_id
WHERE
JobStep.command like ‘%You_SP_or_Table_Name%’
GO
[/sourcecode]

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

3 thoughts on “Search SQL Server Job based on keyword provided”

Comments are closed.