배치정보 (스케줄/command 등)
-- 맵핑 프로시저 등
select sj.name,sjs.step_id,sjs.step_name,sjs.database_name,sjs.command
from msdb..sysjobs sj
left join msdb..sysjobsteps sjs
on sj.job_id = sjs.job_id
where sj.enabled =1
order by sj.name,sjs.step_id
-- 발생 주기 등
SELECT @@servername servername,replace(replace(replace([jobs].name,char(13), ' '),char(10),' '),char(9),' ') as [jobname]
, case jobs.enabled when 1 then 'Y' when 0 then 'N' end as JobEnabled
, replace(replace(replace(CASE convert(nvarchar(3), [sSCH].[enabled]) WHEN 1 THEN 'Y' WHEN 0 THEN 'N' END,char(13), ' '),char(10),' '),char(9),' ') AS [SchedIsEnabled]
, replace(replace(replace(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,char(13), ' '),char(10),' '),char(9),' ') AS [Recurrence]
, replace(replace(replace(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,char(13), ' '),char(10),' '),char(9),' ') [Frequency]
,
CASE WHEN LEN([JobH].run_duration ) IN (1,2) THEN Right([JobH].run_duration ,2)+'초'
WHEN LEN([JobH].run_duration ) = 3 THEN
SUBSTRING(cast([JobH].run_duration as varchar),1,1) + '분 ' + Right([JobH].run_duration ,2) + '초'
WHEN LEN([JobH].run_duration ) = 4 THEN
SUBSTRING(cast([JobH].run_duration as varchar),1,2) + '분 ' + Right([JobH].run_duration ,2) + '초'
WHEN LEN([JobH].run_duration ) = 5 THEN
SUBSTRING(cast([JobH].run_duration as varchar),1,1) + '시간 ' +
SUBSTRING(cast([JobH].run_duration as varchar),2,2) + '분 ' +
Right([JobH].run_duration ,2) + '초'
WHEN LEN([JobH].run_duration ) = 6 THEN
SUBSTRING(cast([JobH].run_duration as varchar),1,2) + '시간 ' +
SUBSTRING(cast([JobH].run_duration as varchar),3,2) + '분 ' +
Right([JobH].run_duration ,2) + '초'
end as [수행시간]
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
LEFT OUTER JOIN
(select * from [msdb].dbo.sysjobhistory AS [JobH] with (nolock)
where instance_id in
(select max(instance_id) as id from [msdb].dbo.sysjobhistory AS [JobH] WITH(NOLOCK)
where step_id=0
group by job_id ) ) JOBH
on jobs.job_id=JOBH.job_id
ORDER BY JobEnabled desc,jobname asc