[MSSQL] JOB(배치잡) 조회 쿼리

SQL Server의 배치를 조회하는 쿼리.

SELECT [jobs].name as [jobname]
    , description
    , CASE  convert(nvarchar(3),[sSCH].[enabled])
        WHEN 1 THEN N'Y'
        WHEN 0 THEN N'N'
      END AS [IsEnabled]
    , CASE 
        WHEN convert(nvarchar(3),[freq_type],3) = 64 THEN N'SQL Server 시작 시 마다'
        WHEN convert(nvarchar(3),[freq_type],3) = 128 THEN N'서버가 유휴 할때마다'
        WHEN convert(nvarchar(3),[freq_type],3) IN (4,8,16,32) THEN N'되풀이'
        WHEN convert(nvarchar(3),[freq_type],3)  = 1 THEN N'한번만'
      END [ScheduleType]
    , CASE convert(nvarchar(3),[freq_type],3) 
        WHEN 1 THEN N'1번'
        WHEN 4 THEN N'매일'
        WHEN 8 THEN N'매주'
        WHEN 16 THEN N'매월'
        WHEN 32 THEN N'매월'
        WHEN 64 THEN N'SQL Server 시작 시 마다'
        WHEN 128 THEN N'서버가 유휴 할때마다'
      END [Occurrence]
    , CASE convert(nvarchar(3),[freq_type],3) 
        WHEN 4 THEN N'실행주기 : N' + CAST([freq_interval] AS VARCHAR(3)) + N' 일(s)'
        WHEN 8 THEN N'실행주기 : N' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                    + N' 주(s) 매주'
                    + CASE WHEN [freq_interval] & 1 = 1 THEN N'일' ELSE N'' END
                    + CASE WHEN [freq_interval] & 2 = 2 THEN N', 월' ELSE N'' END
                    + CASE WHEN [freq_interval] & 4 = 4 THEN N', 화' ELSE N'' END
                    + CASE WHEN [freq_interval] & 8 = 8 THEN N', 수' ELSE N'' END
                    + CASE WHEN [freq_interval] & 16 = 16 THEN N', 목' ELSE N'' END
                    + CASE WHEN [freq_interval] & 32 = 32 THEN N', 금' ELSE N'' END
                    + CASE WHEN [freq_interval] & 64 = 64 THEN N', 토' ELSE N'' END
        WHEN 16 THEN N'하루 한번 N' + CAST([freq_interval] AS VARCHAR(3)) 
                     + N' 모든 N'
                     + CAST([freq_recurrence_factor] AS VARCHAR(3)) + N' 월(S)'
        WHEN 32 THEN N'발생 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
                     + N' N' 
                     + 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' 모든 N' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                     + N' 월(s)'
      END AS [Recurrence]
    , CASE convert(nvarchar(3),[freq_subday_type],3)  
        WHEN 1 THEN N'매일 한번 N' 
              + STUFF(
                 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, N':')
                            , 6, 0, N':')
        WHEN 2 THEN N'주기적 발생 N' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + N' 초(s) 간격 N' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, N':')
                            , 6, 0, N':')
                    + N' & N' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, N':')
                            , 6, 0, N':')
        WHEN 4 THEN N'주기적 발생 N' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + N' 분(s) 간격 N' 
                    + STUFF(
                   STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, N':')
                            , 6, 0, N':')
                    + N' & N' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, N':')
                            , 6, 0, N':')
        WHEN 8 THEN N'주기적 발생 N' 
                    + CAST([freq_subday_interval] AS VARCHAR(3)) + N' 시간(s) 간격 N' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
                                , 3, 0, N':')
                            , 6, 0, N':')
                    + N' & N' 
                    + STUFF(
                    STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6)
                                , 3, 0, N':')
                            , 6, 0, N':')
      END [Frequency]
    , [sSCH].[date_created] AS [ScheduleCreatedOn]
    , [sSCH].[date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysjobschedules] as [sJOBSCH] with(nolock)
    LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [sSCH] with(nolock) ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
    LEFT OUTER JOIN msdb.dbo.sysjobs as [jobs] WITH(NOLOCK) ON [sJOBSCH].job_id=jobs.job_id
ORDER BY jobname

 

결과창