select A.[Job명]
, A.[실행 주기]
, A.[Job 소유자]
, substring(A.[시작 시각], 1, 2) + ':' + substring(A.[시작 시각], 3, 2) + ':' + substring(A.[시작 시각], 5, 2) as [시작 시각]
, substring(A.[종료 시각], 1, 2) + ':' + substring(A.[종료 시각], 3, 2) + ':' + substring(A.[종료 시각], 5, 2) as [종료 시각]
, A.[시작 일자]
, A.[종료 일자]
, A.[Job 생성 일자]
, A.[Job 수정 일자]
, substring(A.[최대 실행 이력], 1, 2) + ':' + substring(A.[최대 실행 이력], 3, 2) + ':' + substring(A.[최대 실행 이력], 5, 2) as [최대 실행 이력]
, A.[Job 설명]
from (
select
a.name as [Job명]
, case when (d.freq_type=4 and d.freq_subday_type=1) then '매일 1회'
when (d.freq_type=4 and d.freq_subday_type=2) then '매일 ' + cast(d.freq_subday_interval as varchar(5)) + '초마다'
when (d.freq_type=4 and d.freq_subday_type=4) then '매일 ' + cast(d.freq_subday_interval as varchar(5)) + '분마다'
when (d.freq_type=4 and d.freq_subday_type=8) then '매일 ' + cast(d.freq_subday_interval as varchar(5)) + '시간마다'
when (d.freq_type=8) then
'매주 ' +
case when d.freq_interval & 1 = 1 then '일 ' else '' end +
case when d.freq_interval & 2 = 2 then '월 ' else '' end +
case when d.freq_interval & 4 = 4 then '화 ' else '' end +
case when d.freq_interval & 8 = 8 then '수 ' else '' end +
case when d.freq_interval & 16 = 16 then '목 ' else '' end +
case when d.freq_interval & 32 = 32 then '금 ' else '' end +
case when d.freq_interval & 64 = 64 then '토 ' else '' end +
case d.freq_subday_type
when 1 then '한번 '
when 2 then '매 ' + cast(d.freq_subday_interval as varchar(5)) + '초마다'
when 4 then '매 ' + cast(d.freq_subday_interval as varchar(5)) + '분마다'
when 8 then '매 ' + cast(d.freq_subday_interval as varchar(5)) + '시간마다'
end
when (d.freq_type=16) then
'매월 ' + cast(d.freq_interval as varchar(2)) + '일 ' +
case d.freq_subday_type
when 1 then '한번 '
when 2 then '매 ' + cast(d.freq_subday_interval as varchar(5)) + '초마다'
when 4 then '매 ' + cast(d.freq_subday_interval as varchar(5)) + '분마다'
when 8 then '매 ' + cast(d.freq_subday_interval as varchar(5)) + '시간마다'
end
else '정의되지 않음'
end [실행 주기]
, b.name as [Job 소유자]
, replicate('0', 6 - datalength(cast(d.active_start_time as varchar(6)))) + cast(d.active_start_time as varchar(6)) as [시작 시각]
, replicate('0', 6 - datalength(cast(d.active_end_time as varchar(6)))) + cast(d.active_end_time as varchar(6)) as [종료 시각]
, convert(varchar(10), convert(datetime, cast(d.active_start_date as varchar(8))), 120) as [시작 일자]
, convert(varchar(10), convert(datetime, cast(d.active_end_date as varchar(8))), 120) as [종료 일자]
, convert(varchar(10), a.date_created, 20) as [Job 생성 일자]
, convert(varchar(10), a.date_modified, 20) as [Job 수정 일자]
, replicate('0', 6 - datalength(cast(c.run_duration as varchar(6)))) + cast(c.run_duration as varchar(6)) as [최대 실행 이력]
, a.description as [Job 설명]
, d.freq_type
, d.freq_subday_type
from msdb.dbo.sysjobs a with (nolock)
left outer join master.dbo.syslogins b with (nolock) on a.owner_sid = b.sid
left outer join (
select job_id, max(run_duration) as run_duration
from msdb.dbo.sysjobhistory with (nolock)
where run_status = 1 and step_id = 0
group by job_id) c on a.job_id = c.job_id
left outer join msdb.dbo.sysjobschedules d with (nolock) on a.job_id = d.job_id
where a.enabled=1 and d.enabled =1
) A
order by A.freq_type, A.freq_subday_type, A.[시작 시각], A.[Job명]
