Friday, May 28, 2010

Get jobs and their schedules

SELECT t1.name AS [Job Name]
  , t1.description AS [Job Description]
  , CASE t1.enabled WHEN 1 THEN 'Yes' ELSE 'No' END AS [Enabled]
  , CASE t3.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 SQL Server Agent starts'
    WHEN 128 THEN 'Start whenever the CPU(s) become idle' END as Occurs
  , t3.active_start_time StartTime
  , t1.date_created AS [Date Created]
  , t1.date_modified AS [Date Modified]  
FROM msdb.dbo.sysjobs t1
 INNER JOIN msdb.dbo.sysjobschedules t2 ON t1.job_id = t2.job_id
 INNER JOIN msdb.dbo.sysschedules t3 ON t2.schedule_id = t3.schedule_id

No comments:

Post a Comment

Oracle Query