Job Schedule view

I'm trying to create a view for a report so that I can easily keep track of all of the backups that I have scheduled on a server. All of these backups are scheduled in subplans of a maintenance plan, so the job names all start with "Backup.".

The problem that I've run into is that I want to easily be able to see when the job is scheduled to run again and how often it's scheduled. The sysschedules table has the fields freq_type and freq_interval. The definition of their values can be found here. As you can see these fields are, for lack of a better way to describe, accumulative. I have no idea on how to, in a set-based solution, set it up so that I can see the names of the day.

What'd I'd like to be able to see is something like "Executes every 3 days" or "Executes every monday, tuesday, friday".

How can I write this so that I'm not using layers of case statements to get what I want?

Here's my code so far:

select 
    j.job_id,
    j.name,
    s.next_run_date,
    s.next_run_time,
    ss.*
from msdb.dbo.sysjobs j
    left outer join msdb.dbo.sysjobschedules s
        on j.job_id=s.job_id
    left outer join msdb.dbo.sysschedules ss
        on s.schedule_id=ss.schedule_id
where j.name like 'backup.%'

There is really no other way than to use a case statement if you want the plain English version of all the integer values used by SQL Server (at least to my limited knowledge of T-SQL). This is a common thing needed by a lot of DBAs and there are plenty of scripts out there on the subject. I found the script below from Michelle Ufford to be quite useful in getting a good view of the schedule of jobs running. Since maintenance plans end up as SQL Agent jobs this will show you that as well.

The output you get from the script will give you the frequency, subFrequency, scheduled time and next run time and date. I just took her script and put it as a stored procedure on my server so I can easily run it when needed. You could also put it as a view if you wanted too. Since other folks can come behind me and add jobs and such I like to check it every-now-and-then to see what is going on.

```
/*
Description: SQL Agent Information
Source: SQLFool, http://sqlfool.com/2012/09/sql-agent-job-script-2/
*/
SET NOCOUNT ON;

DECLARE @jobHistory TABLE (
job_id UNIQUEIDENTIFIER
,success INT
,cancel INT
,fail INT
,retry INT
,last_run_status CHAR(12)
,last_execution_id INT
,last_duration CHAR(8)
,last_execution_start DATETIME
);

WITH lastExecution
AS (
SELECT job_id
,MAX(instance_id) AS last_instance_id
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0
GROUP BY job_id
)
INSERT INTO @jobHistory
SELECT sjh.job_id
,SUM(CASE
WHEN sjh.run_status = 1
AND step_id = 0
THEN 1
ELSE 0
END) AS success
,SUM(CASE
WHEN sjh.run_status = 3
AND step_id = 0
THEN 1
ELSE 0
END) AS cancel
,SUM(CASE
WHEN sjh.run_status = 0
AND step_id = 0
THEN 1
ELSE 0
END) AS fail
,SUM(CASE
WHEN sjh.run_status = 2
THEN 1
ELSE 0
END) AS retry
,CASE sjh.run_status
WHEN 0
THEN 'Failed'
WHEN 1
THEN 'Succeeded'
WHEN 2
THEN 'Retry'
WHEN 3
THEN 'Canceled'
WHEN 4
THEN 'In Progress'
ELSE 'Unknown'
END AS last_run_status
,MAX(CASE
WHEN sjh.step_id = 0
THEN instance_id
ELSE NULL
END) last_execution_id
,SUBSTRING(CAST(MAX(CASE
WHEN le.job_id IS NOT NULL
THEN sjh.run_duration
ELSE NULL
END) + 1000000 AS VARCHAR(7)), 2, 2) + ':' + SUBSTRING(CAST(MAX(CASE
WHEN le.job_id IS NOT NULL
THEN sjh.run_duration
ELSE NULL
END) + 1000000 AS VARCHAR(7)), 4, 2) + ':' + SUBSTRING(CAST(MAX(CASE
WHEN le.job_id IS NOT NULL
THEN sjh.run_duration
ELSE NULL
END) + 1000000 AS VARCHAR(7)), 6, 2) AS last_duration
,MAX(CASE
WHEN le.last_instance_id IS NOT NULL
THEN CONVERT(DATETIME, RTRIM(run_date)) + ((run_time / 10000 31200) + ((run_time % 10000) / 100 60) + (run_time % 10000) % 100) / (86399.9964)
ELSE '1900-01-01'
END) AS last_execution_start
FROM msdb.dbo.sysjobhistory AS sjh
LEFT JOIN lastExecution AS le ON sjh.job_id = le.job_id
AND sjh.instance_id = le.last_instance_id
GROUP BY sjh.job_id
,sjh.run_status;

/ We need to parse the schedule into something we can understand /
DECLARE @weekDay TABLE (
mask INT
,maskValue VARCHAR(32)
);

INSERT INTO @weekDay
SELECT 1
,'Sunday'

UNION ALL

SELECT 2
,'Monday'

UNION ALL

SELECT 4
,'Tuesday'

UNION ALL

SELECT 8
,'Wednesday'

UNION ALL

SELECT 16
,'Thursday'

UNION ALL

SELECT 32
,'Friday'

UNION ALL

SELECT 64
,'Saturday';

/ Now let's get our schedule information /
WITH myCTE
AS (
SELECT sched.name AS 'scheduleName'
,sched.schedule_id
,jobsched.job_id
,CASE
WHEN sched.freq_type = 1
THEN 'Once'
WHEN sched.freq_type = 4
AND sched.freq_interval = 1
THEN 'Daily'
WHEN sched.freq_type = 4
THEN 'Every ' + CAST(sched.freq_interval AS VARCHAR(5)) + ' days'
WHEN sched.freq_type = 8
THEN REPLACE(REPLACE(REPLACE((
SELECT maskValue
FROM @weekDay AS x
WHERE sched.freq_interval & x.mask <> 0
ORDER BY mask
FOR XML RAW
), '"/>', '') + CASE
WHEN sched.freq_recurrence_factor <> 0

StackExchange Database Administrators Q#10018, answer score: 6