http://www.sqlservercentral.com/Forums/Topic410557-116-1.aspx
/*******************************************************************************
Name: GetJobSchedule (For SQL Server7.0&2000)
Author: M.Pearson
Creation Date: 5 Jun 2002
Version: 1.0
Program Overview: This queries the sysjobs, sysjobschedules and sysjobhistory table to
produce a resultset showing the jobs on a server plus their schedules
(if applicable) and the maximun duration of the job.
The UNION join is to cater for jobs that have been scheduled but not yet
run, as this information is stored in the 'active_start...' fields of the
sysjobschedules table, whereas if the job has already run the schedule
information is stored in the 'next_run...' fields of the sysjobschedules table.
Modification History:
-------------------------------------------------------------------------------
Version Date Name Modification
-------------------------------------------------------------------------------
1.0 5 Jun 2002 M.Pearson Inital Creation
1.1 6 May 2009 A. Gonzalez Adapted to SQL Server 2005 and to show
subday frequencies.
*******************************************************************************/
USE msdb
Go
SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' +
substring(convert(varchar(15),active_start_date),5,2) + '/' +
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(active_start_time,3),1)
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_start_time,5),1)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
END,
-- active_start_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0
UNION
SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = CASE len(next_run_time)
WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,
-- next_run_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0
ORDER BY [Start Date],[Start Time]
--------------------------------
or for some more information like next run time
----------------------
select
'Server' = left(@@ServerName,20),
'JobName' = left(S.name,30),
'ScheduleName' = left(ss.name,25),
'Enabled' = CASE (S.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
'Frequency' = CASE(ss.freq_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)
WHEN 16 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)
WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE
WHEN 64 THEN 'SQL Startup'
WHEN 128 THEN 'SQL Idle'
ELSE '??'
END,
'Interval' = CASE
WHEN (freq_type = 1) then 'One time only'
WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
from (select ss.schedule_id,
freq_interval,
'D1' = CASE WHEN (freq_interval & 1 <> 0) then 'Sun ' ELSE '' END,
'D2' = CASE WHEN (freq_interval & 2 <> 0) then 'Mon ' ELSE '' END,
'D3' = CASE WHEN (freq_interval & 4 <> 0) then 'Tue ' ELSE '' END,
'D4' = CASE WHEN (freq_interval & 8 <> 0) then 'Wed ' ELSE '' END,
'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu ' ELSE '' END,
'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri ' ELSE '' END,
'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat ' ELSE '' END
from msdb..sysschedules ss
where freq_type = 8
) as F
where schedule_id = sj.schedule_id
)
WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
WHEN (freq_type = 32) then (select freq_rel + WDAY
from (select ss.schedule_id,
'freq_rel' = CASE(freq_relative_interval)
WHEN 1 then 'First'
WHEN 2 then 'Second'
WHEN 4 then 'Third'
WHEN 8 then 'Fourth'
WHEN 16 then 'Last'
ELSE '??'
END,
'WDAY' = CASE (freq_interval)
WHEN 1 then ' Sun'
WHEN 2 then ' Mon'
WHEN 3 then ' Tue'
WHEN 4 then ' Wed'
WHEN 5 then ' Thu'
WHEN 6 then ' Fri'
WHEN 7 then ' Sat'
WHEN 8 then ' Day'
WHEN 9 then ' Weekday'
WHEN 10 then ' Weekend'
ELSE '??'
END
from msdb..sysschedules ss
where ss.freq_type = 32
) as WS
where WS.schedule_id =ss.schedule_id
)
END,
'Time' = CASE (freq_subday_type)
WHEN 1 then left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
ELSE '??'
END,
'Next Run Time' = CASE SJ.next_run_date
WHEN 0 THEN cast('n/a' as char(10))
ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
END
from msdb.dbo.sysjobschedules SJ
join msdb.dbo.sysjobs S on S.job_id = SJ.job_id
join msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id
order by S.name
/*******************************************************************************
Name: GetJobSchedule (For SQL Server7.0&2000)
Author: M.Pearson
Creation Date: 5 Jun 2002
Version: 1.0
Program Overview: This queries the sysjobs, sysjobschedules and sysjobhistory table to
produce a resultset showing the jobs on a server plus their schedules
(if applicable) and the maximun duration of the job.
The UNION join is to cater for jobs that have been scheduled but not yet
run, as this information is stored in the 'active_start...' fields of the
sysjobschedules table, whereas if the job has already run the schedule
information is stored in the 'next_run...' fields of the sysjobschedules table.
Modification History:
-------------------------------------------------------------------------------
Version Date Name Modification
-------------------------------------------------------------------------------
1.0 5 Jun 2002 M.Pearson Inital Creation
1.1 6 May 2009 A. Gonzalez Adapted to SQL Server 2005 and to show
subday frequencies.
*******************************************************************************/
USE msdb
Go
SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' +
substring(convert(varchar(15),active_start_date),5,2) + '/' +
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 1 THEN cast('00:00:0' + right(active_start_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(active_start_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(active_start_time,3),1)
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_start_time,5),1)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
END,
-- active_start_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules
ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time = 0
UNION
SELECT dbo.sysjobs.Name AS 'Job Name',
'Job Enabled' = CASE dbo.sysjobs.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Frequency' = CASE dbo.sysschedules.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END,
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = CASE len(next_run_time)
WHEN 1 THEN cast('00:00:0' + right(next_run_time,2) as char(8))
WHEN 2 THEN cast('00:00:' + right(next_run_time,2) as char(8))
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,
-- next_run_time as 'Start Time',
CASE len(run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(run_duration,3),1)
+':' + right(run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(run_duration,5),1)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(run_duration,6),2)
+':' + Left(right(run_duration,4),2)
+':' + right(run_duration,2) as char (8))
END as 'Max Duration',
CASE(dbo.sysschedules.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE cast('Every '
+ right(dbo.sysschedules.freq_subday_interval,2)
+ ' '
+ CASE(dbo.sysschedules.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END as char(16))
END as 'Subday Frequency'
FROM dbo.sysjobs
LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id
INNER JOIN dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration
FROM dbo.sysjobhistory
GROUP BY job_id) Q1
ON dbo.sysjobs.job_id = Q1.job_id
WHERE Next_run_time <> 0
ORDER BY [Start Date],[Start Time]
--------------------------------
or for some more information like next run time
----------------------
select
'Server' = left(@@ServerName,20),
'JobName' = left(S.name,30),
'ScheduleName' = left(ss.name,25),
'Enabled' = CASE (S.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
'Frequency' = CASE(ss.freq_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)
WHEN 16 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)
WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE
WHEN 64 THEN 'SQL Startup'
WHEN 128 THEN 'SQL Idle'
ELSE '??'
END,
'Interval' = CASE
WHEN (freq_type = 1) then 'One time only'
WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
from (select ss.schedule_id,
freq_interval,
'D1' = CASE WHEN (freq_interval & 1 <> 0) then 'Sun ' ELSE '' END,
'D2' = CASE WHEN (freq_interval & 2 <> 0) then 'Mon ' ELSE '' END,
'D3' = CASE WHEN (freq_interval & 4 <> 0) then 'Tue ' ELSE '' END,
'D4' = CASE WHEN (freq_interval & 8 <> 0) then 'Wed ' ELSE '' END,
'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu ' ELSE '' END,
'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri ' ELSE '' END,
'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat ' ELSE '' END
from msdb..sysschedules ss
where freq_type = 8
) as F
where schedule_id = sj.schedule_id
)
WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
WHEN (freq_type = 32) then (select freq_rel + WDAY
from (select ss.schedule_id,
'freq_rel' = CASE(freq_relative_interval)
WHEN 1 then 'First'
WHEN 2 then 'Second'
WHEN 4 then 'Third'
WHEN 8 then 'Fourth'
WHEN 16 then 'Last'
ELSE '??'
END,
'WDAY' = CASE (freq_interval)
WHEN 1 then ' Sun'
WHEN 2 then ' Mon'
WHEN 3 then ' Tue'
WHEN 4 then ' Wed'
WHEN 5 then ' Thu'
WHEN 6 then ' Fri'
WHEN 7 then ' Sat'
WHEN 8 then ' Day'
WHEN 9 then ' Weekday'
WHEN 10 then ' Weekend'
ELSE '??'
END
from msdb..sysschedules ss
where ss.freq_type = 32
) as WS
where WS.schedule_id =ss.schedule_id
)
END,
'Time' = CASE (freq_subday_type)
WHEN 1 then left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
ELSE '??'
END,
'Next Run Time' = CASE SJ.next_run_date
WHEN 0 THEN cast('n/a' as char(10))
ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
END
from msdb.dbo.sysjobschedules SJ
join msdb.dbo.sysjobs S on S.job_id = SJ.job_id
join msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id
order by S.name
No comments:
Post a Comment