Technical Article

List Jobs and their respective programming.

,

I created the following script which will allow us to list all active Jobs in SQL Server with the details of the schedule for the implementation of each of them, this information is helpful to keep the documentation of our servers SQL Server.

This script is also posted on my personal blog which is http://recursossqlserver.blogspot.com

This Job has been tested in SQL Server 2005 and 2008 R2.

SELECT sysjobs.name AS NOMBRE_JOB, CASE freq_type 
WHEN 1 THEN 'Una sola vez'
WHEN 4 THEN 'Diariamente'
WHEN 8 THEN 'Semanalmente'
WHEN 16 THEN 'Mensualmente'
WHEN 32 THEN 'Mensualmente'
WHEN 64 THEN 'Se ejecuta cuando se inicia el servicio del Agente SQL Server'
WHEN 128 THEN 'Se ejecuta cuando el equipo está inactivo ' END AS FRECUENCIA, CASE freq_type 
WHEN 4 THEN 'Cada ' + CAST(freq_interval  AS VARCHAR) + ' Día(s).'
WHEN 8 THEN 
CASE WHEN freq_interval& 1 = 1 THEN 'Domingo ' ELSE '' END + 
CASE WHEN freq_interval& 2 = 2 THEN 'Lunes ' ELSE '' END + 
CASE WHEN freq_interval& 4 = 4 THEN 'Martes ' ELSE '' END + 
CASE WHEN freq_interval& 8 = 8 THEN 'Miércoles ' ELSE '' END + 
CASE WHEN freq_interval& 16 = 16 THEN 'Jueves ' ELSE '' END + 
CASE WHEN freq_interval& 32 = 32 THEN 'Viernes ' ELSE '' END + 
CASE WHEN freq_interval& 64 = 64 THEN 'Sábado ' ELSE '' END + 'cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' semana(s).'
WHEN 16 THEN 'En el día '  + CAST(freq_interval  AS VARCHAR) + ' de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).'
WHEN 32 THEN 
CASE WHEN freq_relative_interval = 1 THEN 'Cada primer ' ELSE '' END +
CASE WHEN freq_relative_interval = 2 THEN 'Cada segundo ' ELSE '' END +
CASE WHEN freq_relative_interval = 4 THEN 'Cada tercer ' ELSE '' END +
CASE WHEN freq_relative_interval = 8 THEN 'Cada cuarto ' ELSE '' END +
CASE WHEN freq_relative_interval = 16 THEN 'Cada último ' ELSE '' END +
CASE freq_interval
WHEN 1 THEN 'Domingo de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).'
WHEN 2 THEN 'Lunes de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).' 
WHEN 3 THEN 'Martes de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).' 
WHEN 4 THEN 'Miércoles de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).' 
WHEN 5 THEN 'Jueves de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).' 
WHEN 6 THEN 'Viernes de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).' 
WHEN 7 THEN 'Sábado de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).'
WHEN 8 THEN 'día de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).' 
WHEN 9 THEN 'día de la semana de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).' 
WHEN 10 THEN 'fin de semana de cada ' + CAST(freq_recurrence_factor  AS VARCHAR) + ' mes(es).' END END AS PROGRAMACION, 
CAST(CAST(sysschedules.active_start_time / 10000 AS VARCHAR(2)) + ':' + CAST(LEFT(RIGHT(sysschedules.active_start_time, 4), 2) 
AS VARCHAR(2)) + ':' + CAST(RIGHT(sysschedules.active_start_time, 2) AS VARCHAR(2)) AS TIME(0)) AS HORA_EJECUCION
FROM sysschedules INNER JOIN
sysjobschedules ON sysschedules.schedule_id = sysjobschedules.schedule_id RIGHT OUTER JOIN
sysjobs ON sysjobschedules.job_id = sysjobs.job_id
WHERE (sysjobs.enabled = 1)
ORDER BY HORA_EJECUCION

Rate

2.75 (4)

Share

Share

Rate

2.75 (4)