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.


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

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.

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

  1. Pingback: Query to Find missing job in SQL Server

  2. Pingback: Milestone of 500+ article

Comments are closed.