아래의 쿼리의 일정처럼 쿼리로 뽑을수 없을까요?
구글링을 해봤지만 저렇게 자세하게는 나오지 않더군요....
그렇다고 조합하자니 case 문을 너무 연발하고;;;
Comment 3
-
이리
2016.03.11 10:14
-
Terry
2016.03.11 11:15
-
군고구마
2016.03.11 15:44
정말 감사합니다.
어제 밤에 해보다가 안되서 오늘 아침에 찾아보고 만들었습니다.
혹시 필요한 분들을 위해서 댓글로 남겨둡니다.
SELECT
[jobs].name as [jobname]
, description
, CASE convert(nvarchar(3),[sSCH].[enabled])
WHEN 1 THEN 'Y'
WHEN 0 THEN 'N'
END AS [IsEnabled]
, CASE
WHEN convert(nvarchar(3),[freq_type],3) = 64 THEN 'SQL Server 시작 시 마다'
WHEN convert(nvarchar(3),[freq_type],3) = 128 THEN '서버가 유휴 할때마다'
WHEN convert(nvarchar(3),[freq_type],3) IN (4,8,16,32) THEN '되풀이'
WHEN convert(nvarchar(3),[freq_type],3) = 1 THEN '한번만'
END [ScheduleType]
, CASE convert(nvarchar(3),[freq_type],3)
WHEN 1 THEN '1번'
WHEN 4 THEN '매일'
WHEN 8 THEN '매주'
WHEN 16 THEN '매월'
WHEN 32 THEN '매월'
WHEN 64 THEN 'SQL Server 시작 시 마다'
WHEN 128 THEN '서버가 유휴 할때마다'
END [Occurrence]
, CASE convert(nvarchar(3),[freq_type],3)
WHEN 4 THEN '실행주기 : ' + CAST([freq_interval] AS VARCHAR(3)) + ' 일(s)'
WHEN 8 THEN '실행주기 : ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ' 주(s) 매주'
+ CASE WHEN [freq_interval] & 1 = 1 THEN '일' 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 ', 목' ELSE '' END
+ CASE WHEN [freq_interval] & 32 = 32 THEN ', 금' ELSE '' END
+ CASE WHEN [freq_interval] & 64 = 64 THEN ', 토' ELSE '' END
WHEN 16 THEN '하루 한번 ' + CAST([freq_interval] AS VARCHAR(3))
+ ' 모든 '
+ CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' 월(S)'
WHEN 32 THEN '발생 '
+ CASE [freq_relative_interval]
WHEN 1 THEN '첫째'
WHEN 2 THEN '둘째'
WHEN 4 THEN '셋째'
WHEN 8 THEN '넷째'
WHEN 16 THEN '마지막'
END
+ ' '
+ CASE [freq_interval]
WHEN 1 THEN '일'
WHEN 2 THEN '월'
WHEN 3 THEN '화'
WHEN 4 THEN '수'
WHEN 5 THEN '목'
WHEN 6 THEN '금'
WHEN 7 THEN '토'
WHEN 8 THEN '일'
WHEN 9 THEN '평일'
WHEN 10 THEN '주말'
END
+ ' 모든 ' + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ' 월(s)'
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 '주기적 발생 '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' 초(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 '주기적 발생 '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' 분(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 '주기적 발생 '
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ' 시간(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]
, [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
http://www.sqler.com/271911
이런 방법은 어떤지요