Data mining SQL Server Agent

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 jobs.name='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:

SELECT name
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

enabled=1

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.