VanillaGranilla

getSQLSAjobsSchedules.sql

May 11th, 2016
256
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.21 KB | None | 0 0
  1. SELECT DISTINCT substring(a.name,1,100) AS [Job Name],
  2.     'Enabled'=case
  3.     WHEN a.enabled = 0 THEN 'No'
  4.     WHEN a.enabled = 1 THEN 'Yes'
  5.     end,
  6.         substring(b.name,1,30) AS [Name of the schedule],
  7.     'Frequency of the schedule execution'=case
  8.     WHEN b.freq_type = 1 THEN 'Once'
  9.     WHEN b.freq_type = 4 THEN 'Daily'
  10.     WHEN b.freq_type = 8 THEN 'Weekly'
  11.     WHEN b.freq_type = 16 THEN 'Monthly'
  12.     WHEN b.freq_type = 32 THEN 'Monthly relative'  
  13.     WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'
  14.     END,   
  15.     'Units for the freq_subday_interval'=case
  16.     WHEN b.freq_subday_type = 1 THEN 'At the specified time'
  17.     WHEN b.freq_subday_type = 2 THEN 'Seconds'
  18.     WHEN b.freq_subday_type = 4 THEN 'Minutes'
  19.     WHEN b.freq_subday_type = 8 THEN 'Hours'
  20.     END,   
  21.     cast(cast(b.active_start_date as varchar(15)) as datetime) as active_start_date,   
  22.     cast(cast(b.active_end_date as varchar(15)) as datetime) as active_end_date,   
  23.     Stuff(Stuff(right('000000'+Cast(c.next_run_time as Varchar),6),3,0,':'),6,0,':') as Run_Time,  
  24.     convert(varchar(24),b.date_created) as Created_Date
  25.    
  26. FROM  msdb.dbo.sysjobs a
  27. INNER JOIN msdb.dbo.sysJobschedules c ON a.job_id = c.job_id
  28. INNER JOIN msdb.dbo.SysSchedules b on b.Schedule_id=c.Schedule_id
Advertisement
Add Comment
Please, Sign In to add comment