Click here to Skip to main content
12,758,605 members (31,162 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


2 bookmarked
Posted 23 Nov 2010

Data mining SQL Server Agent

, 24 Nov 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Search job commands for text, find jobs by schedule and other spiffy things
In my particular situation, one of our main databases is being overhauled, resulting in the task of moving certain stored procedures to other schemas or databases. To do this we had to search the jobs in Agent to see which ones needed to be rewritten. The query turned out to be simple:
SELECT jobs.Name
FROM msdb.dbo.sysjobs jobs LEFT OUTER JOIN msdb.dbo.sysjobsteps steps
    ON jobs.job_id=steps.job_id
WHERE steps.command LIKE '%search text%'

So let's suppose you needed to move a stored procedure named sp_DoSomething. Use the code above with the procedure name in the LIKE clause will give you the jobs (if any) that execute sp_DoSomething.

The table msdb.dbo.sysjobs holds data about Agent jobs. Each step in the job is kept in msdb.dbo.sysjobsteps. You can get a listing of all the steps in a job with
SELECT steps.step_id, steps.step_name, steps.command
FROM msdb.dbo.sysjobs jobs LEFT OUTER JOIN msdb.dbo.sysjobsteps steps
    ON jobs.job_id=steps.job_id
WHERE'job name'
ORDER BY steps.step_id

Another useful table is sysschedules, which holds the scheduling information for Agent jobs. The help file for this table gives more details, but if you wanted to find out what jobs run on the weekend, the query would look like this:
FROM msdb.dbo.sysschedules
WHERE freq_type=4 -- these run every day
OR (freq_type=8 -- these run weekly
     AND (freq_interval=1 -- on Sunday
        OR freq_interval=7 -- on Saturday
        OR freq_interval=10)) -- on both weekend days

If you want to return only active jobs, add

There are two other tables that are worth a look. sysjobschedule gives the list of upcoming jobs, including the next scheduled date and time, and sysjobhistory is your reference to past job execution with an entry for each job step giving date, time, how long the step took to run, the result of execution and other useful info.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Gregory Gadow
Software Developer (Senior)
United States United States
Gregory Gadow lives in Seattle, Washington and has been writing code for almost 25 years in more than a dozen programming languages. He works for a mid-size brokerage firm and holds the Series 7 and Series 66 brokerage licenses, but much prefers working as the company's programming department doing VB6, VB.Net, ASP, HTML, XML and SQL.

You may also be interested in...


Comments and Discussions

GeneralReason for my vote of 3 average Pin
joshijigar29-Nov-10 7:28
memberjoshijigar29-Nov-10 7:28 
GeneralReason for my vote of 5 Thank you for sharing. Awesome! Pin
Slacker00724-Nov-10 1:10
memberSlacker00724-Nov-10 1:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170217.1 | Last Updated 24 Nov 2010
Article Copyright 2010 by Gregory Gadow
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid