매주 월요일 08시에 실행되는 Job이 뭐죠?

만약 누가 저런 질문을 한다면 어떻게 찾아야 할까요?
작업 하나 하나 속성 대화상자를 열어서 일정을 확인해야 하나요??
근데 작업이 100개가 넘어가면??

그래서 모든 Job에 대한 Step과 Schedule을 한번에 조회할 수 있는 쿼리를 만들어 봤습니다.
하나의 쿼리와 두개의 펑션으로 이루어져 있습니다.
CREATE FUNCTION DBO.FUNC_job_schedule(@schedule_id int) RETURNS VARCHAR(8000)

AS

BEGIN
        DECLARE @schedule VARCHAR(1000)

        DECLARE @freq_type INT
        DECLARE @freq_interval INT
        DECLARE @freq_subday_type INT
        DECLARE @freq_subday_interval VARCHAR(3)
        DECLARE @freq_relative_interval INT
        DECLARE @freq_recurrence_factor VARCHAR(3)
        DECLARE @active_start_date VARCHAR(10)
        DECLARE @active_end_date VARCHAR(10)
        DECLARE @active_start_time VARCHAR(8)
        DECLARE @active_end_time VARCHAR(8)

        SELECT @freq_type = freq_type
                , @freq_interval = freq_interval
                , @freq_subday_type = freq_subday_type
                , @freq_subday_interval = CONVERT(VARCHAR(3), freq_subday_interval)
                , @freq_relative_interval = freq_relative_interval
                , @freq_recurrence_factor = CONVERT(VARCHAR(3), freq_recurrence_factor)
                , @active_start_date = STUFF(STUFF(CONVERT(VARCHAR(8), active_start_date), 7, 0, '-'), 5, 0, '-')
                , @active_end_date = STUFF(STUFF(CONVERT(VARCHAR(8), active_end_date), 7, 0, '-'), 5, 0, '-')
                , @active_start_time = STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), active_start_time), 6), 5, 0, ':'), 3, 0, ':')
                , @active_end_time = STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), active_end_time), 6), 5, 0, ':'), 3, 0, ':')
        FROM    msdb.dbo.sysschedules
        WHERE   schedule_id = @schedule_id

        IF @freq_type = 1 BEGIN
                SET @schedule = @active_start_date + ' ' + @active_start_time + '에 발생합니다.'
        END ELSE IF @freq_type = 4 BEGIN
                SET @schedule = CASE WHEN @freq_interval = '1' THEN '매일 ' ELSE '매 ' + CONVERT(VARCHAR(3), @freq_interval) + '일마다 ' END
        END ELSE IF @freq_type = 8 BEGIN
                SET @schedule = CASE WHEN @freq_recurrence_factor = '1' THEN '매주 ' ELSE '매 ' + @freq_recurrence_factor + '주마다 ' END

                DECLARE @weekday VARCHAR(30)

                SET @weekday = ''

                IF @freq_interval >= 64 BEGIN SET @weekday = ',토'; SET @freq_interval = @freq_interval - 64 END
                IF @freq_interval >= 32 BEGIN SET @weekday = ',금' + @weekday; SET @freq_interval = @freq_interval - 32 END
                IF @freq_interval >= 16 BEGIN SET @weekday = ',목' + @weekday; SET @freq_interval = @freq_interval - 16 END
                IF @freq_interval >= 8 BEGIN SET @weekday = ',수' + @weekday; SET @freq_interval = @freq_interval - 8 END
                IF @freq_interval >= 4 BEGIN SET @weekday = ',화' + @weekday; SET @freq_interval = @freq_interval - 4 END
                IF @freq_interval >= 2 BEGIN SET @weekday = ',월' + @weekday; SET @freq_interval = @freq_interval - 2 END
                IF @freq_interval = 1 SET @weekday = @weekday + ',일'

                SET @schedule = @schedule + SUBSTRING(@weekday, 2, 30) + '요일 '
        END ELSE IF @freq_type = 16 BEGIN
                SET @schedule = CASE WHEN @freq_recurrence_factor = '1' THEN '매월 ' ELSE '매 ' + @freq_recurrence_factor + '개월마다 그달의 ' END
                SET @schedule = @schedule + CONVERT(VARCHAR(3), @freq_interval) + '일 '
        END ELSE IF @freq_type = 32 BEGIN
                SET @schedule = '매 ' + @freq_recurrence_factor + '개월마다 '

                IF @freq_relative_interval = 1 SET @schedule = @schedule + '첫 번째'
                ELSE IF @freq_relative_interval = 2 SET @schedule = @schedule + '두 번째'
                ELSE IF @freq_relative_interval = 4 SET @schedule = @schedule + '세 번째'
                ELSE IF @freq_relative_interval = 8 SET @schedule = @schedule + '네 번째'
                ELSE IF @freq_relative_interval = 16 SET @schedule = @schedule + '마지막'

                SET @schedule = @schedule + ' '

                IF @freq_interval = 1 SET @schedule = @schedule + '일요일'
                ELSE IF @freq_interval = 2 SET @schedule = @schedule + '월요일'
                ELSE IF @freq_interval = 3 SET @schedule = @schedule + '화요일'
                ELSE IF @freq_interval = 4 SET @schedule = @schedule + '수요일'
                ELSE IF @freq_interval = 5 SET @schedule = @schedule + '목요일'
                ELSE IF @freq_interval = 6 SET @schedule = @schedule + '금요일'
                ELSE IF @freq_interval = 7 SET @schedule = @schedule + '토요일'
                ELSE IF @freq_interval = 8 SET @schedule = @schedule + '일'
                ELSE IF @freq_interval = 9 SET @schedule = @schedule + '평일'
                ELSE IF @freq_interval = 10 SET @schedule = @schedule + '주말'

                SET @schedule = @schedule + ' '
        END ELSE IF @freq_type = 64 BEGIN
                SET @schedule = 'SQL Server 에이전트가 시작될 때 자동으로 시작'
        END ELSE IF @freq_type = 128 BEGIN
                SET @schedule = 'CPU가 유휴 상태로 될 때마다 시작'
        END

        IF @freq_type IN (4, 8, 16, 32) BEGIN
                IF @freq_subday_type = 1 BEGIN
                        SET @schedule = @schedule + @active_start_time + '에 발생합니다.'
                END ELSE BEGIN
                        SET @schedule = @schedule + @freq_subday_interval + ' ' + CASE @freq_subday_type WHEN 2 THEN '초' WHEN 4 THEN '분' WHEN 8 THEN '시간' END
                        SET @schedule = @schedule + '(' + @active_start_time + '에서 ' + @active_end_time + ' 사이) 간격으로 발생합니다.'
                END

                IF @active_end_date = '9999-12-31' BEGIN
                        SET @schedule = @schedule + ' 일정은 ' + @active_start_date + '부터 사용됩니다.'
                END ELSE BEGIN
                        SET @schedule = @schedule + ' 일정은 ' + @active_start_date + '에서 ' + @active_end_date + ' 사이에 사용됩니다.'
                END
        END

        RETURN @schedule
END

GO

CREATE FUNCTION DBO.FUNC_job_steps(@job_id uniqueidentifier) RETURNS VARCHAR(8000)

AS

BEGIN
        DECLARE @steps VARCHAR(8000)

        SET @steps = ''

        SELECT   @steps = CHAR(10) + '[' + CONVERT(VARCHAR(3), step_id) + ':' + step_name + ']' + CHAR(10) + command
        FROM     msdb.dbo.sysjobsteps
        WHERE    job_id = @job_id
        ORDER BY step_id

        RETURN @steps
END

GO

CREATE PROC USP_S_jobs

AS

SELECT  A.job_id
        , job_name = A.name
        , job_enabled = A.enabled
        , A.description
        , A.date_created
        , A.date_modified
        , A.version_number
        , A.start_step_id
        , steps = DBO.FUNC_job_steps(A.job_id)
        , C.schedule_id
        , C.schedule_uid
        , schedule_name = C.name
        , schedule_enabled = C.enabled
        , schedule = DBO.FUNC_job_schedule(C.schedule_id)
        , next_run_date = STUFF(STUFF(CONVERT(VARCHAR(8), B.next_run_date), 7, 0, '-'), 5, 0, '-')
        , next_run_time = STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), B.next_run_time), 6), 5, 0, ':'), 3, 0, ':')
        , C.date_created
        , C.date_modified
        , C.version_number

FROM    msdb.dbo.sysjobs A
        LEFT OUTER JOIN msdb.dbo.sysjobschedules B ON A.job_id = B.job_id
        LEFT OUTER JOIN msdb.dbo.sysschedules C ON B.schedule_id = C.schedule_id

ORDER BY A.name

GO

EXEC USP_S_jobs
다음은 실행 결과입니다.

스케줄을 한번에 볼 수 있어서 언제 실행되는 파악하기 용이합니다.