SQL Server 에이전트에 등록된 작업 목록 조회

SQL Server 에이전트에 등록된 작업 목록을 조회 하는 SQL 입니다.

SELECT
	A.name,
	case description when '설명이 없습니다.' then '' else description end as description,   
  CASE convert(
    nvarchar(3), 
    [freq_type], 
    3
  ) WHEN 4 THEN N' 매일' WHEN 8 THEN N' 매주 ' + CASE WHEN [freq_interval] & 1 = 1 THEN N'일' ELSE '' END 
	+ CASE WHEN [freq_interval] & 2 = 2 THEN ' 월' ELSE '' END + CASE WHEN [freq_interval] & 4 = 4 THEN ' 화' ELSE '' END + CASE WHEN [freq_interval] & 8 = 8 THEN ' 수' ELSE '' END + CASE WHEN [freq_interval] & 16 = 16 THEN N' 목' ELSE '' END + CASE WHEN [freq_interval] & 32 = 32 THEN N' 금' ELSE '' END + CASE WHEN [freq_interval] & 64 = 64 THEN N' 토' ELSE '' END WHEN 16 THEN N'하루 한번 ' 
	+ CAST(
    [freq_interval] AS VARCHAR(3)
  ) + N' 매달 ' + CAST(
    [freq_recurrence_factor] AS VARCHAR(3)
  ) + N' 매달' WHEN 32 THEN N'발생 ' + CASE [freq_relative_interval] WHEN 1 THEN N'첫째' WHEN 2 THEN N'둘째' WHEN 4 THEN N'셋째' WHEN 8 THEN N'넷째' WHEN 16 THEN N'마지막' END + ' ' + CASE [freq_interval] WHEN 1 THEN N'일' WHEN 2 THEN N'월' WHEN 3 THEN N'화' WHEN 4 THEN N'수' WHEN 5 THEN N'목' WHEN 6 THEN N'금' WHEN 7 THEN N'토' WHEN 8 THEN N'일' WHEN 9 THEN N'평일' WHEN 10 THEN N'주말' END + N' 모든 ' + CAST(
    [freq_recurrence_factor] AS VARCHAR(3)
  ) + N' 매달' END AS [Recurrence], 
  CASE convert(
    nvarchar(3), 
    [freq_subday_type], 
    3
  ) WHEN 1 THEN  STUFF(
    STUFF(
      RIGHT(
        '000000' + CAST(
          [active_start_time] AS VARCHAR(6)
        ), 
        6
      ), 
      3, 
      0, 
      ':'
    ), 
    6, 
    0, 
    ':'
  ) WHEN 2 THEN N'주기적 발생 ' + CAST(
    [freq_subday_interval] AS VARCHAR(3)
  ) + N' 초(s) 간격 ' + STUFF(
    STUFF(
      RIGHT(
        '000000' + CAST(
          [active_start_time] AS VARCHAR(6)
        ), 
        6
      ), 
      3, 
      0, 
      ':'
    ), 
    6, 
    0, 
    ':'
  ) + ' & ' + STUFF(
    STUFF(
      RIGHT(
        '000000' + CAST(
          [active_end_time] AS VARCHAR(6)
        ), 
        6
      ), 
      3, 
      0, 
      ':'
    ), 
    6, 
    0, 
    ':'
  ) WHEN 4 THEN N'주기적 발생 ' + CAST(
    [freq_subday_interval] AS VARCHAR(3)
  ) + N' 분(s) 간격 ' + STUFF(
    STUFF(
      RIGHT(
        '000000' + CAST(
          [active_start_time] AS VARCHAR(6)
        ), 
        6
      ), 
      3, 
      0, 
      ':'
    ), 
    6, 
    0, 
    ':'
  ) + ' & ' + STUFF(
    STUFF(
      RIGHT(
        '000000' + CAST(
          [active_end_time] AS VARCHAR(6)
        ), 
        6
      ), 
      3, 
      0, 
      ':'
    ), 
    6, 
    0, 
    ':'
  ) WHEN 8 THEN N'주기적 발생 ' + CAST(
    [freq_subday_interval] AS VARCHAR(3)
  ) + N' 시간(s) 간격 ' + STUFF(
    STUFF(
      RIGHT(
        '000000' + CAST(
          [active_start_time] AS VARCHAR(6)
        ), 
        6
      ), 
      3, 
      0, 
      ':'
    ), 
    6, 
    0, 
    ':'
  ) + ' & ' + STUFF(
    STUFF(
      RIGHT(
        '000000' + CAST(
          [active_end_time] AS VARCHAR(6)
        ), 
        6
      ), 
      3, 
      0, 
      ':'
    ), 
    6, 
    0, 
    ':'
  ) END [Frequency],  D.step_id, D.step_name, D.command 
FROM msdb.dbo.sysjobs A 
	INNER JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id 
	INNER JOIN msdb.dbo.sysschedules C ON B.schedule_id = C.schedule_id 
	INNER JOIN msdb.dbo.sysjobsteps D ON A.job_id = D.job_id  
ORDER BY A.name

 

실행 결과는 대략 아래와 같이 조회가 됩니다.