Microsoft doesn't provide a way toSELECT the job schedule descriptions. They do
provide a stored procedure[msdb.dbo.sp_get_schedule_description] which will return
a job schedule description for one job.
This script's select statement was derived from information in that procedure, and lists
all jobs on a server with their name, description and schedule description.
USE msdb
GO
/*
Microsoft doesn't provide a way to SELECT the job schedule descriptions. They do
provide a stored procedure [msdb.dbo.sp_get_schedule_description] which will return
a job schedule description for one job.
The below select statement was derived from information in that procedure, and lists
all jobs on a server with their name, description and schedule description.
*/
SELECT
j.name
, j.description
, CASE
WHEN j.enabled = 0 THEN 'Disabled'
WHEN s.job_id IS NULL THEN 'Unscheduled'
WHEN s.freq_type = 0x1 -- OneTime
THEN
'Once on '
+ CONVERT(
CHAR(10)
, CAST( CAST( s.active_start_date AS VARCHAR ) AS DATETIME )
, 102 -- yyyy.mm.dd
)
WHEN s.freq_type = 0x4 -- Daily
THEN 'Daily'
WHEN s.freq_type = 0x8 -- weekly
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN 'Weekly on '
WHEN s.freq_recurrence_factor > 1
THEN 'Every '
+ CAST( s.freq_recurrence_factor AS VARCHAR )
+ ' weeks on '
END
+ LEFT(
CASE WHEN s.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END
, LEN(
CASE WHEN s.freq_interval & 1 = 1 THEN 'Sunday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 2 = 2 THEN 'Monday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 4 = 4 THEN 'Tuesday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 8 = 8 THEN 'Wednesday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 16 = 16 THEN 'Thursday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 32 = 32 THEN 'Friday, ' ELSE '' END
+ CASE WHEN s.freq_interval & 64 = 64 THEN 'Saturday, ' ELSE '' END
) - 1 -- LEN() ignores trailing spaces
)
WHEN s.freq_type = 0x10 -- monthly
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN 'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN 'Every '
+ CAST( s.freq_recurrence_factor AS VARCHAR )
+ ' months on the '
END
+ CAST( s.freq_interval AS VARCHAR )
+ CASE
WHEN s.freq_interval IN ( 1, 21, 31 ) THEN 'st'
WHEN s.freq_interval IN ( 2, 22 ) THEN 'nd'
WHEN s.freq_interval IN ( 3, 23 ) THEN 'rd'
ELSE 'th'
END
WHEN s.freq_type = 0x20 -- monthly relative
THEN
CASE
WHEN s.freq_recurrence_factor = 1
THEN 'Monthly on the '
WHEN s.freq_recurrence_factor > 1
THEN 'Every '
+ CAST( s.freq_recurrence_factor AS VARCHAR )
+ ' months on the '
END
+ CASE s.freq_relative_interval
WHEN 0x01 THEN 'first '
WHEN 0x02 THEN 'second '
WHEN 0x04 THEN 'third '
WHEN 0x08 THEN 'fourth '
WHEN 0x10 THEN 'last '
END
+ CASE s.freq_interval
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
WHEN 8 THEN 'day'
WHEN 9 THEN 'week day'
WHEN 10 THEN 'weekend day'
END
WHEN s.freq_type = 0x40
THEN 'Automatically starts when SQLServerAgent starts.'
WHEN s.freq_type = 0x80
THEN 'Starts whenever the CPUs become idle'
ELSE ''
END
+ CASE
WHEN j.enabled = 0 THEN ''
WHEN s.job_id IS NULL THEN ''
WHEN s.freq_subday_type = 0x1 OR s.freq_type = 0x1
THEN ' at '
+ CASE
WHEN s.active_start_time = 0 THEN '12:00'
WHEN s.active_start_time = 120000 THEN '12:00'
WHEN s.active_start_time < 100000
THEN STUFF(
LEFT( CAST ( s.active_start_time AS VARCHAR ), 3 )
, 2
, 0
, ':'
)
WHEN s.active_start_time < 120000
THEN STUFF(
LEFT( CAST ( s.active_start_time AS VARCHAR ), 4 )
, 3
, 0
, ':'
)
WHEN s.active_start_time < 220000
THEN STUFF(
LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 3 )
, 2
, 0
, ':'
)
ELSE STUFF(
LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 4 )
, 3
, 0
, ':'
)
END
+ CASE
WHEN s.active_start_time < 120000 THEN ' AM'
ELSE ' PM'
END
WHEN s.freq_subday_type IN ( 0x2, 0x4, 0x8 )
THEN ' every '
+ CAST( s.freq_subday_interval AS VARCHAR )
+ CASE freq_subday_type
WHEN 0x2 THEN ' second'
WHEN 0x4 THEN ' minute'
WHEN 0x8 THEN ' hour'
END
+ CASE
WHEN s.freq_subday_interval > 1 THEN 's'
END
ELSE ''
END
+ CASE
WHEN j.enabled = 0 THEN ''
WHEN s.job_id IS NULL THEN ''
WHEN s.freq_subday_type IN ( 0x2, 0x4, 0x8 )
THEN ' between '
+ CASE
WHEN s.active_start_time = 0 THEN '12:00'
WHEN s.active_start_time = 120000 THEN '12:00'
WHEN s.active_start_time < 100000
THEN STUFF(
LEFT( CAST ( s.active_start_time AS VARCHAR ), 3 )
, 2
, 0
, ':'
)
WHEN s.active_start_time < 120000
THEN STUFF(
LEFT( CAST ( s.active_start_time AS VARCHAR ), 4 )
, 3
, 0
, ':'
)
WHEN s.active_start_time < 220000
THEN STUFF(
LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 3 )
, 2
, 0
, ':'
)
ELSE STUFF(
LEFT( CAST ( s.active_start_time - 120000 AS VARCHAR ), 4 )
, 3
, 0
, ':'
)
END
+ CASE
WHEN s.active_start_time < 120000 THEN ' AM'
ELSE ' PM'
END
+ ' and '
+ CASE
WHEN s.active_end_time = 0 THEN '12:00'
WHEN s.active_end_time = 120000 THEN '12:00'
WHEN s.active_end_time < 100000
THEN STUFF(
LEFT( CAST ( s.active_end_time AS VARCHAR ), 3 )
, 2
, 0
, ':'
)
WHEN s.active_end_time < 120000
THEN STUFF(
LEFT( CAST ( s.active_end_time AS VARCHAR ), 4 )
, 3
, 0
, ':'
)
WHEN s.active_end_time < 220000
THEN STUFF(
LEFT( CAST ( s.active_end_time - 120000 AS VARCHAR ), 3 )
, 2
, 0
, ':'
)
ELSE STUFF(
LEFT( CAST ( s.active_end_time - 120000 AS VARCHAR ), 4 )
, 3
, 0
, ':'
)
END
+ CASE
WHEN s.active_end_time < 120000 THEN ' AM'
ELSE ' PM'
END
ELSE ''
END AS schedule
FROM
dbo.sysjobs j WITH (nolock)
LEFT JOIN dbo.sysjobschedules s WITH (nolock) ON s.job_id = j.job_id
AND s.enabled = 1
ORDER BY
j.name 
Quick and hassle free method of viewing the existing or non-existant primary keys in your database.
2005-11-16 (first published: 2005-10-28)
229 reads
The purpose of this task is to generate a report showing all your foreign keys. Generally this would be considered nothing special only that this script also returns the tables, column foreign key constraint name and the relationship properties.Here's how to do it:First of all copy the script and paste it in Query AnalyzerStep 1: […]
2004-07-16 (first published: 2004-07-02)
997 reads
This Stored Procedure creates the script of all existing triggersOptionally it creates the script to drop the triggers too.Triggers may belong to different owners. This procedure takes care of it.In SQLServer Enterprise Manager there is a facility to generate scripts forTables, Views, Stored Procedures etc.But it won't generate script for triggers unless we include the […]
2002-03-27
2,651 reads
When doing bulk data changes it may be beneficial to disable indexes prior to starting the operation. Fortunately, SQL's rich metadata makes this very easy to automate in a robust fashion.
2018-05-18 (first published: 2015-10-19)
4,786 reads
If you think about it carefully, you’ll realise, that index maintenance is necessary and that it makes sense, and that SQL Server is not doing this task automatically according to the book.
2011-08-18
5,492 reads