背景：数据库服务器定期重启，想知道重启期间对作业的影响。通俗点就是服务器在重启的这段时间，有哪些作业计划运行，重启后是否需要手动执行这些作业？
第一次重启的时候，按照最笨的方式，把所有作业看一遍，然后人为判断有哪些作业将受到影响，再根据作业具体代码，确定是否需手动执行。后来老大说要弄个过程出来，通过传入起止时间参数，返回区间内的作业计划。
PS:参考各类资料，修改过很多遍，最后成型在6月初，很多细节上的修改自己也记不清楚了，一直懒得整理。先放上代码，以及效果图。
1 /************************************************************ 2 * 说明：输入将来的起、止时间，得到时间区间内作业计划情况。主要包括： 3 区间内首次执行时间、区间内末次执行时间、区间内执行次数、循环间隔、每天执行频率等。 4 * 使用：DBA_Pro_GetJobSchedules '20140612 07:00:00.000','20140612 08:00:00.000' 5 * 补充：手动运行作业，不会影响作业的计划调度。 6 ************************************************************/ 7 CREATE PROC DBA_Pro_GetJobSchedules 8 @Starttime DATETIME, 9 @Endtime DATETIME 10 AS 11 12 DECLARE @Midtime DATETIME 13 IF (@Starttime < GETDATE() OR @Starttime >= @Endtime) 14 BEGIN 15 PRINT('请输入将来的起、止时间，且开始时间小于结束时间！') 16 RETURN 17 END 18 19 CREATE TABLE #RunningJobs 20 ( 21 name VARCHAR(128)--作业名称 22 ,schedule_id INT--调度id 23 ,next_scheduled_run_date DATETIME--下次运行时间 yyyy-mm-dd hh:mi:ss:mmm 24 ,active_start_time DATETIME--执行间隔:开始时间 yyyy-mm-dd hh:mi:ss:mmm 25 ,active_end_time DATETIME--执行间隔:结束时间 yyyy-mm-dd hh:mi:ss:mmm 26 ,starttime DATETIME--运行区间开始时间,比较传入的@Starttime和active_start_time，取大者 27 ,endtime DATETIME--运行区间结束时间,比较传入的@Endtime和active_end_time，取小者 28 ,daystatus INT--日期状态,用于标记当天是否满足作业运行日期，为0时当天将运行 29 ,timestaus INT--时间状态,用于标记当天只执行一次的作业是否已运行，为0时当天将运行 30 ) 31 32 WHILE (@Starttime < @Endtime) 33 BEGIN 34 SELECT @Midtime = CASE 35 WHEN DATEADD(DAY ,1 ,@Starttime) > @Endtime THEN @Endtime 36 ELSE CONVERT(VARCHAR ,DATEADD(DAY ,1 ,@starttime) ,112) 37 END 38 39 INSERT INTO #RunningJobs 40 SELECT sj.name 41 ,scd.schedule_id 42 ,c.next_scheduled_run_date 43 ,CONVERT( 44 DATETIME 45 ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' + 46 STUFF( 47 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)) ,6),3,0,':') 48 ,6,0,':' 49 ) 50 ) [active_start_time] 51 ,CONVERT( 52 DATETIME 53 ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' + 54 STUFF( 55 STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)) ,6),3,0,':') 56 ,6,0,':' 57 ) 58 ) [active_end_time] 59 ,CASE 60 WHEN @Starttime < 61 CONVERT( 62 DATETIME 63 ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' + 64 STUFF( 65 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)) ,6),3,0,':') 66 ,6,0,':' 67 ) 68 ) THEN CONVERT( 69 DATETIME 70 ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' + 71 STUFF( 72 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)) ,6),3,0,':') 73 ,6,0,':' 74 ) 75 ) 76 ELSE @Starttime 77 END starttime 78 ,CASE 79 WHEN @Midtime > 80 CONVERT( 81 DATETIME 82 ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' + 83 STUFF( 84 STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)) ,6),3,0,':') 85 ,6,0,':' 86 ) 87 ) THEN CONVERT( 88 DATETIME 89 ,CONVERT(VARCHAR(10) ,@Starttime ,120) + ' ' + 90 STUFF( 91 STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)) ,6),3,0,':') 92 ,6,0,':' 93 ) 94 ) 95 ELSE @Midtime 96 END endtime 97 ,CASE freq_type 98 WHEN 1 --OneTime 99 THEN DATEDIFF(day ,next_scheduled_run_date ,@Starttime) 100 WHEN 4 --Daily 101 THEN DATEDIFF(dd ,next_scheduled_run_date ,@Starttime)%freq_interval 102 WHEN 8 --Weekly 103 THEN DATEDIFF(week ,next_scheduled_run_date ,@Starttime)%freq_recurrence_factor 104 + 105 CASE 106 WHEN freq_interval & 1 = POWER(2 ,DATEPART(dw ,@Starttime) -1) 107 OR freq_interval & 2 = POWER(2 ,DATEPART(dw ,@Starttime) -1) 108 OR freq_interval & 4 = POWER(2 ,DATEPART(dw ,@Starttime) -1) 109 OR freq_interval & 8 = POWER(2 ,DATEPART(dw ,@Starttime) -1) 110 OR freq_interval & 16 = POWER(2 ,DATEPART(dw ,@Starttime) -1) 111 OR freq_interval & 32 = POWER(2 ,DATEPART(dw ,@Starttime) -1) 112 OR freq_interval & 64 = POWER(2 ,DATEPART(dw ,@Starttime) -1) THEN 0 113 END 114 WHEN 16 --Monthly 115 THEN DATEDIFF(MONTH ,next_scheduled_run_date ,@Starttime)%freq_recurrence_factor 116 + 117 CASE 118 WHEN freq_interval = DAY(@Starttime) THEN 0 119 END 120 WHEN 32 --Monthly - Relative to Frequency Interval 121 THEN DATEDIFF(MONTH ,next_scheduled_run_date ,@Starttime)%freq_recurrence_factor 122 + 123 CASE 124 WHEN freq_interval = DATEPART(dw ,@Starttime) --month of week1-7 125 AND 126 ( 127 ( 128 freq_relative_interval <= 8 129 AND freq_relative_interval = POWER( 130 2 131 ,DATEDIFF(DAY ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' ,@Starttime) / 7 132 ) 133 ) 134 OR ( 135 freq_relative_interval = 16 136 AND DATEDIFF( 137 DAY 138 ,@Starttime 139 ,DATEADD( 140 DAY 141 ,-1 142 ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 143 ) 144 ) < 7 145 ) 146 ) THEN 0 147 WHEN freq_interval = 8 --month of day 148 AND 149 ( 150 ( 151 freq_relative_interval = 1 152 AND DATEDIFF(DAY ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' ,@Starttime) = 0 153 ) 154 OR ( 155 freq_relative_interval = 2 156 AND DATEDIFF(DAY ,CONVERT(CHAR(6) ,@Starttime ,112) + '02' ,@Starttime) = 0 157 ) 158 OR ( 159 freq_relative_interval = 4 160 AND DATEDIFF(DAY ,CONVERT(CHAR(6) ,@Starttime ,112) + '03' ,@Starttime) = 0 161 ) 162 OR ( 163 freq_relative_interval = 8 164 AND DATEDIFF(DAY ,CONVERT(CHAR(6) ,@Starttime ,112) + '04' ,@Starttime) = 0 165 ) 166 OR ( 167 freq_relative_interval = 16 168 AND CONVERT(VARCHAR ,@Starttime ,112) = 169 CONVERT( 170 VARCHAR 171 ,DATEADD( 172 DAY 173 ,-1 174 ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 175 ) 176 ,112 177 ) 178 ) 179 ) THEN 0 180 WHEN freq_interval = 9 --month of weekday 181 AND 182 ( 183 ( 184 freq_relative_interval = 1 185 AND DATEADD( 186 DAY 187 ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 188 WHEN 1 THEN 1 189 WHEN 7 THEN 2 190 ELSE 0 191 END 192 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' 193 ) = CONVERT(VARCHAR ,@Starttime ,112) 194 ) 195 OR ( 196 freq_relative_interval = 2 197 AND DATEADD( 198 DAY 199 ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 200 WHEN 6 THEN 3 201 WHEN 7 THEN 3 202 WHEN 1 THEN 2 203 ELSE 1 204 END 205 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' 206 ) = CONVERT(VARCHAR ,@Starttime ,112) 207 ) 208 OR ( 209 freq_relative_interval = 4 210 AND DATEADD( 211 DAY 212 ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 213 WHEN 5 THEN 4 214 WHEN 6 THEN 4 215 WHEN 7 THEN 4 216 WHEN 1 THEN 3 217 ELSE 2 218 END 219 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' 220 ) = CONVERT(VARCHAR ,@Starttime ,112) 221 ) 222 OR ( 223 freq_relative_interval = 8 224 AND DATEADD( 225 DAY 226 ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 227 WHEN 4 THEN 5 228 WHEN 5 THEN 5 229 WHEN 6 THEN 5 230 WHEN 7 THEN 5 231 WHEN 1 THEN 4 232 ELSE 3 233 END 234 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' 235 ) = CONVERT(VARCHAR ,@Starttime ,112) 236 ) 237 OR ( 238 freq_relative_interval = 16 239 AND DATEADD( 240 DAY 241 ,CASE DATEPART( 242 dw 243 ,DATEADD( 244 DAY 245 ,-1 246 ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 247 ) 248 ) 249 WHEN 1 THEN -2 250 WHEN 7 THEN -1 251 ELSE 0 252 END 253 ,DATEADD( 254 DAY 255 ,-1 256 ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 257 ) 258 ) = CONVERT(VARCHAR ,@Starttime ,112) 259 ) 260 ) THEN 0 261 WHEN freq_interval = 10 --month of weekend 262 AND 263 ( 264 ( 265 freq_relative_interval = 1 266 AND DATEADD( 267 DAY 268 ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 269 WHEN 2 THEN 5 270 WHEN 3 THEN 4 271 WHEN 4 THEN 3 272 WHEN 5 THEN 2 273 WHEN 6 THEN 1 274 ELSE 0 275 END 276 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' 277 ) = CONVERT(VARCHAR ,@Starttime ,112) 278 ) 279 OR ( 280 freq_relative_interval = 2 281 AND DATEADD( 282 DAY 283 ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 284 WHEN 3 THEN 5 285 WHEN 4 THEN 4 286 WHEN 5 THEN 3 287 WHEN 6 THEN 2 288 WHEN 7 THEN 1 289 ELSE 6 290 END 291 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' 292 ) = CONVERT(VARCHAR ,@Starttime ,112) 293 ) 294 OR ( 295 freq_relative_interval = 4 296 AND DATEADD( 297 DAY 298 ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 299 WHEN 2 THEN 12 300 WHEN 3 THEN 11 301 WHEN 4 THEN 10 302 WHEN 5 THEN 9 303 WHEN 6 THEN 8 304 ELSE 7 305 END 306 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' 307 ) = CONVERT(VARCHAR ,@Starttime ,112) 308 ) 309 OR ( 310 freq_relative_interval = 8 311 AND DATEADD( 312 DAY 313 ,CASE DATEPART(dw ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 314 WHEN 3 THEN 12 315 WHEN 4 THEN 11 316 WHEN 5 THEN 10 317 WHEN 6 THEN 9 318 WHEN 7 THEN 8 319 ELSE 13 320 END 321 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01' 322 ) = CONVERT(VARCHAR ,@Starttime ,112) 323 ) 324 OR ( 325 freq_relative_interval = 16 326 AND DATEADD( 327 DAY 328 ,CASE DATEPART( 329 dw 330 ,DATEADD( 331 DAY 332 ,-1 333 ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 334 ) 335 ) 336 WHEN 2 THEN -1 337 WHEN 3 THEN -2 338 WHEN 4 THEN -3 339 WHEN 5 THEN -4 340 WHEN 6 THEN -5 341 ELSE 0 342 END 343 ,DATEADD( 344 DAY 345 ,-1 346 ,DATEADD(MONTH ,1 ,CONVERT(CHAR(6) ,@Starttime ,112) + '01') 347 ) 348 ) = CONVERT(VARCHAR ,@Starttime ,112) 349 ) 350 ) THEN 0 351 END 352 END AS daystatus 353 ,CASE 354 WHEN freq_subday_type IN (0 ,1) 355 THEN 356 CASE 357 WHEN active_start_time*1000 358 >= CONVERT(INT ,REPLACE(CONVERT(VARCHAR ,@Starttime ,114) ,':' ,'')) 359 THEN 0 360 ELSE 1 361 END 362 ELSE 0 363 END AS timestaus 364 FROM [msdb].[dbo].[sysschedules] scd 365 INNER JOIN msdb.dbo.sysjobschedules sjsc 366 ON scd.schedule_id = sjsc.schedule_id 367 INNER JOIN ( 368 SELECT a.job_id 369 ,a.next_scheduled_run_date 370 FROM msdb.dbo.sysjobactivity a 371 INNER JOIN ( 372 SELECT MAX(session_id) session_id 373 FROM msdb.dbo.syssessions 374 ) b 375 ON a.session_id = b.session_id 376 ) c 377 ON sjsc.job_id = c.job_id 378 INNER JOIN msdb.dbo.sysjobs sj 379 ON c.job_id = sj.job_id 380 WHERE scd.enabled = 1 381 AND sj.enabled = 1 382 --AND c.next_scheduled_run_date <= @Midtime 383 384 SET @Starttime = CONVERT(VARCHAR ,DATEADD(DAY ,1 ,@starttime) ,112) 385 END 386 --select * from #RunningJobs order by name,active_start_time 387 SELECT name--作业名称 388 ,plan_type--计划类型 389 ,frequency--执行频率每天、每周、每月 390 ,next_scheduled_run_date--下次计划运行时间 391 ,FirstRunTimeInRange--区间内首次运行时间 392 ,LastRunTimeInRange--区间内末次运行时间 393 ,RunTimesInRange--区间内运行次数 394 ,intercycle--循环间隔 395 ,execution_interval--每天执行频率及区间 396 ,active_start_date--开始日期 397 ,active_end_date--结束日期 398 ,date_created--创建时间 399 ,date_modified--修改时间 400 FROM ( 401 SELECT a.name 402 ,a.schedule_id 403 ,a.active_start_time 404 ,a.starttime 405 ,a.endtime 406 ,freq_type,freq_subday_type 407 ,freq_subday_interval 408 ,CASE 409 WHEN [freq_type] = 64 THEN 410 'Start automatically when SQL Server Agent starts' 411 WHEN [freq_type] = 128 THEN 412 'Start whenever the CPUs become idle' 413 WHEN [freq_type] IN (4 ,8 ,16 ,32) THEN 'Recurring' 414 WHEN [freq_type] = 1 THEN 'One Time' 415 END AS plan_type 416 ,CASE [freq_type] 417 WHEN 1 THEN 'One Time' 418 WHEN 4 THEN 'Daily' 419 WHEN 8 THEN 'Weekly' 420 WHEN 16 THEN 'Monthly' 421 WHEN 32 THEN 'Monthly - Relative to Frequency Interval' 422 WHEN 64 THEN 'Start automatically when SQL Server Agent starts' 423 WHEN 128 THEN 'Start whenever the CPUs become idle' 424 END AS frequency 425 ,a.next_scheduled_run_date 426 ,CASE [freq_subday_type] 427 WHEN 2 THEN DATEADD( 428 second 429 ,( 430 freq_subday_interval - 431 DATEDIFF(second ,a.active_start_time ,a.starttime) 432 %freq_subday_interval 433 )%freq_subday_interval 434 ,a.starttime 435 ) 436 WHEN 4 THEN DATEADD( 437 second 438 ,( 439 freq_subday_interval * 60 - 440 DATEDIFF(second ,a.active_start_time ,a.starttime) 441 %(freq_subday_interval * 60) 442 )%(freq_subday_interval * 60) 443 ,a.starttime 444 ) 445 WHEN 8 THEN DATEADD( 446 second 447 ,( 448 freq_subday_interval * 3600 - 449 DATEDIFF(second ,a.active_start_time ,a.starttime) 450 %(freq_subday_interval * 3600) 451 )%(freq_subday_interval * 3600) 452 ,a.starttime 453 ) 454 ELSE a.active_start_time 455 END AS FirstRunTimeInRange 456 ,CASE [freq_subday_type] 457 WHEN 2 THEN DATEADD( 458 second 459 ,-DATEDIFF(second ,a.active_start_time ,a.endtime) 460 %freq_subday_interval 461 ,a.endtime 462 ) 463 WHEN 4 THEN DATEADD( 464 second 465 ,-DATEDIFF(second ,a.active_start_time ,a.endtime) 466 %(freq_subday_interval * 60) 467 ,a.endtime 468 ) 469 WHEN 8 THEN DATEADD( 470 second 471 ,-DATEDIFF(second ,a.active_start_time ,a.endtime) 472 %(freq_subday_interval * 3600) 473 ,a.endtime 474 ) 475 ELSE a.active_start_time 476 END AS LastRunTimeInRange 477 ,CASE [freq_subday_type] 478 WHEN 2 THEN DATEDIFF( 479 second 480 ,DATEADD( 481 second 482 ,( 483 freq_subday_interval - 484 DATEDIFF(second ,a.active_start_time ,a.starttime) 485 %freq_subday_interval 486 )%freq_subday_interval 487 ,a.starttime 488 ) 489 ,a.endtime 490 ) / (freq_subday_interval) + 1 491 WHEN 4 THEN DATEDIFF( 492 second 493 ,DATEADD( 494 second 495 ,( 496 freq_subday_interval * 60 - 497 DATEDIFF(second ,a.active_start_time ,a.starttime) 498 %(freq_subday_interval * 60) 499 )%(freq_subday_interval * 60) 500 ,a.starttime 501 ) 502 ,a.endtime 503 ) / (freq_subday_interval * 60) + 1 504 WHEN 8 THEN DATEDIFF( 505 second 506 ,DATEADD( 507 second 508 ,( 509 freq_subday_interval * 3600 - 510 DATEDIFF(second ,a.active_start_time ,a.starttime) 511 %(freq_subday_interval * 3600) 512 )%(freq_subday_interval * 3600) 513 ,a.starttime 514 ) 515 ,a.endtime 516 ) / (freq_subday_interval * 3600) + 1 517 ELSE 1 518 END AS RunTimesInRange 519 ,CASE [freq_type] 520 WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) 521 + ' day(s)' 522 WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 523 + ' week(s) on ' 524 + CASE 525 WHEN [freq_interval] & 1 = 1 THEN 'Sunday' 526 ELSE '' 527 END 528 + CASE 529 WHEN [freq_interval] & 2 = 2 THEN ', Monday' 530 ELSE '' 531 END 532 + CASE 533 WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' 534 ELSE '' 535 END 536 + CASE 537 WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' 538 ELSE '' 539 END 540 + CASE 541 WHEN [freq_interval] & 16 = 16 THEN ', Thursday' 542 ELSE '' 543 END 544 + CASE 545 WHEN [freq_interval] & 32 = 32 THEN ', Friday' 546 ELSE '' 547 END 548 + CASE 549 WHEN [freq_interval] & 64 = 64 THEN ', Saturday' 550 ELSE '' 551 END 552 WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) 553 + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 554 + ' month(s)' 555 WHEN 32 THEN 'Occurs on ' + CASE [freq_relative_interval] 556 WHEN 1 THEN 'First' 557 WHEN 2 THEN 'Second' 558 WHEN 4 THEN 'Third' 559 WHEN 8 THEN 'Fourth' 560 WHEN 16 THEN 'Last' 561 END + ' ' + CASE 562 [freq_interval] 563 WHEN 1 THEN 564 'Sunday' 565 WHEN 2 THEN 566 'Monday' 567 WHEN 3 THEN 568 'Tuesday' 569 WHEN 4 THEN 570 'Wednesday' 571 WHEN 5 THEN 572 'Thursday' 573 WHEN 6 THEN 574 'Friday' 575 WHEN 7 THEN 576 'Saturday' 577 WHEN 8 THEN 578 'Day' 579 WHEN 9 THEN 580 'Weekday' 581 WHEN 10 THEN 582 'Weekend day' 583 END + 584 ' of every ' 585 + CAST([freq_recurrence_factor] AS VARCHAR(3)) + 586 ' month(s)' 587 END AS intercycle 588 ,CASE [freq_subday_type] 589 WHEN 1 THEN 'Occurs once at ' + CONVERT(VARCHAR ,a.active_start_time ,108) 590 WHEN 2 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) 591 + ' Second(s) between ' + CONVERT(VARCHAR ,a.active_start_time ,108) 592 + ' and ' + CONVERT(VARCHAR ,a.active_end_time ,108) 593 WHEN 4 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) 594 + ' Minute(s) between ' + CONVERT(VARCHAR ,a.active_start_time ,108) 595 + ' and ' + CONVERT(VARCHAR ,a.active_end_time ,108) 596 WHEN 8 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) 597 + ' Hour(s) between ' + CONVERT(VARCHAR ,a.active_start_time ,108) 598 + ' and ' + CONVERT(VARCHAR ,a.active_end_time ,108) 599 END AS execution_interval 600 ,STUFF( 601 STUFF(CAST([active_start_date] AS VARCHAR(8)) ,5 ,0 ,'-') 602 ,8,0,'-' 603 ) AS active_start_date 604 ,STUFF( 605 STUFF(CAST([active_end_date] AS VARCHAR(8)) ,5 ,0 ,'-') 606 ,8,0,'-' 607 ) AS active_end_date 608 ,scd.[date_created] AS date_created 609 ,scd.[date_modified] AS date_modified 610 FROM #RunningJobs a 611 INNER JOIN [msdb].[dbo].[sysschedules] scd 612 ON a.schedule_id = scd.schedule_id 613 WHERE a.daystatus = 0 614 AND a.timestaus=0 615 ) a 616 WHERE a.FirstRunTimeInRange <= a.endtime 617 AND a.FirstRunTimeInRange <= CONVERT(DATETIME ,a.active_end_date + ' 23:59:59:997') 618 ORDER BY 619 a.name 620 ,a.FirstRunTimeInRange 621 622 DROP TABLE #RunningJobs
执行过程将返回作业区间内首次执行时间、区间内末次执行时间、区间内执行次数、循环间隔、每天执行频率等信息
--15:31 2015/1/14 如果我们想把一个操作加到某个00:00:00执行作业的最后一步，我们先要把零点的那些作业找出来，此时可以利用msdb.dbo.sysjobschedules查询作业下次执行时间。注意msdb.dbo.sysjobschedules由后台线程每20分钟更新一次。
1 --下次执行时间(后台线程每20分钟更新) 2 select top 5 sj.name,sjs.next_run_date,sjs.next_run_time from msdb.dbo.sysjobs sj 3 inner join msdb.dbo.sysjobschedules sjs 4 on sj.job_id=sjs.job_id 5 where sj.enabled=1 6 and next_run_time=0--根据需求调整 7 order by sjs.next_run_date,sjs.next_run_time
对于执行频率很高的作业，msdb.dbo.sysjobschedules中的下次运行时间会滞后，如果需要实时的信息可以从msdb.dbo.sysjobactivity中获取。SQL Server代理服务每次重启后，将创建一个新的会话，并且 msdb 数据库的 sysjobactivity 表由所有现有的已定义作业填充。
1 --下次执行时间(实时更新) 2 select top 5 sj.name,sja.next_scheduled_run_date from msdb.dbo.sysjobs sj 3 inner join 4 (select * from msdb.dbo.sysjobactivity 5 where session_id in(select max(session_id) session_id from msdb.dbo.sysjobactivity) 6 )sja 7 on sj.job_id=sja.job_id 8 where sj.enabled=1 9 order by sja.next_scheduled_run_date
实时更新的代码对应“SQL Server 代理->作业活动监视器”的内容，里面可以设置筛选、排序。