列出by月周日執行的schedule
-- list jobs and schedule info with daily and weekly schedules
-- jobs with a daily schedule
select
sysjobs.name job_name
,sysschedules.name schedule_name
,case
when freq_type = 4 then 'Daily'
end frequency
,
'every ' + cast (freq_interval as varchar(3)) + ' day(s)' Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type = 4 and sysjobs.enabled=1 and sysschedules.enabled=1
union
-- jobs with a weekly schedule
select
sysjobs.name job_name
,sysschedules.name schedule_name
,case
when freq_type = 8 then 'Weekly'
end frequency
,
( CASE WHEN freq_interval&1 = 1 THEN '日' ELSE '' END
+CASE WHEN freq_interval&2 = 2 THEN '一' ELSE '' END
+CASE WHEN freq_interval&4 = 4 THEN '二' ELSE '' END
+CASE WHEN freq_interval&8 = 8 THEN '三' ELSE '' END
+CASE WHEN freq_interval&16 = 16 THEN '四' ELSE '' END
+CASE WHEN freq_interval&32 = 32 THEN '五' ELSE '' END
+CASE WHEN freq_interval&64 = 64 THEN '六' ELSE '' END
) Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type = 8 and sysjobs.enabled=1 and sysschedules.enabled=1
union all
-- jobs with a monthly schedule
select
sysjobs.name job_name
,sysschedules.name schedule_name
,case
when freq_type = 4 then 'Daily'
when freq_type = 8 then 'Weekly'
when freq_type = 16 then 'Monthly'
when freq_type = 32 then 'Monthly'
end frequency
,
case
when freq_type = 32 then
(
case
when freq_relative_interval = 1 then 'First '
when freq_relative_interval = 2 then 'Second '
when freq_relative_interval = 4 then 'Third '
when freq_relative_interval = 8 then 'Fourth '
when freq_relative_interval = 16 then 'Last '
end
+
case when freq_interval = 1 THEN 'Sunday, ' ELSE '' END
+case when freq_interval = 2 THEN 'Monday, ' ELSE '' END
+case when freq_interval = 3 THEN 'Tuesday, ' ELSE '' END
+case when freq_interval = 4 THEN 'Wednesday, ' ELSE '' END
+case when freq_interval = 5 THEN 'Thursday, ' ELSE '' END
+case when freq_interval = 6 THEN 'Friday, ' ELSE '' END
+case when freq_interval = 7 THEN 'Saturday, ' ELSE '' END
+case when freq_interval = 8 THEN 'Day of Month, ' ELSE '' END
+case when freq_interval = 9 THEN 'Weekday, ' ELSE '' END
+case when freq_interval = 10 THEN 'Weekend day, ' ELSE '' END
)
)
else cast(freq_interval as varchar(3)) END Days
,
case
when freq_subday_type = 2 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' seconds' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 4 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' minutes' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
when freq_subday_type = 8 then ' every ' + cast(freq_subday_interval as varchar(7))
+ ' hours' + ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
else ' starting at '
+ stuff(stuff(RIGHT(replicate('0', 6) + cast(active_start_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')
end time
from msdb.dbo.sysjobs
inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
where freq_type in (16, 32) and sysjobs.enabled=1 and sysschedules.enabled=1
order by job_name,schedule_name
Taiwan is a country. 臺灣是我的國家
Taiwan is a country. 臺灣是我的國家