2K와 스키마 일부 변경
set nocount on
go
DECLARE job_cursor CURSOR
FOR
SELECT job_id FROM sysjobs ORDER BY [name] asc
OPEN job_cursor
DECLARE @job_id uniqueidentifier
create table #tmp (iSeq int identity(1,1) not null, tmp text not null)
declare @job varchar(8000)
set @job = ''
FETCH NEXT FROM job_cursor INTO @job_id
WHILE @@FETCH_STATUS = 0
BEGIN
select @job = @job +
'===' + name + '==='
+ '
' +
CASE enabled
WHEN 0 THEN '(사용중지)'
WHEN 1 THEN ''
END
from sysjobs
where job_id = @job_id
select @job = @job + '
*' +
isnull(
case freq_type
when 1 then '한 번만 '
when 4 then '매일 '
when 8 then '매주' +
case freq_interval
when 1 then ' 일요일'
when 2 then ' 월요일'
when 4 then ' 화요일'
when 8 then ' 수요일'
when 16 then ' 목요일'
when 32 then ' 금요일'
when 64 then ' 토요일'
end
when 16 then '매월' + convert(char(2),freq_interval) + '일'
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
when 64 then 'SQL Server Agent가 시작될 때 실행'
end,
'''''''check''''''') +
CASE freq_subday_type
WHEN 1 then SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),1,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),3,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),5,2) + ' 에'
WHEN 2 then SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),1,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),3,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),5,2) + ' 부터 ' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_end_time))+1,6,convert(char(6),active_end_time)),1,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_end_time))+1,6,convert(char(6),active_end_time)),3,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_end_time))+1,6,convert(char(6),active_end_time)),5,2) + ' 까지 ' + convert(varchar(100),freq_subday_interval) + '초마다'
WHEN 4 then SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),1,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),3,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),5,2) + ' 부터 ' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_end_time))+1,6,convert(char(6),active_end_time)),1,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_end_time))+1,6,convert(char(6),active_end_time)),3,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_end_time))+1,6,convert(char(6),active_end_time)),5,2) + ' 까지 ' + convert(varchar(100),freq_subday_interval) + '분마다'
WHEN 8 then SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),1,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),3,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),5,2) + ' 부터 ' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_end_time))+1,6,convert(char(6),active_end_time)),1,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_end_time))+1,6,convert(char(6),active_end_time)),3,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_end_time))+1,6,convert(char(6),active_end_time)),5,2) + ' 까지 ' + convert(varchar(100),freq_subday_interval) + '시간마다'
WHEN 0 then SUBSTRING(convert(char(8),active_start_date),1,4)+'-'+SUBSTRING(convert(char(8),active_start_date),5,2)+'-'+SUBSTRING(convert(char(8),active_start_date),7,2)+' '+SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),1,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),3,2) + ':' + SUBSTRING(STUFF('000000',6-len(convert(char(6),active_start_time))+1,6,convert(char(6),active_start_time)),5,2) + ' 에'
END
from sysjobs as j inner join sysjobschedules as sjs
on j.job_id = sjs.job_id
inner join sysschedules as js
on js.schedule_id = sjs.schedule_id
where j.job_id = @job_id
select @job = @job +
'
# ''''''' + step_name + ''''''' <br>' + replace(rtrim(ltrim(command)),'
','<br>')
from sysjobsteps where job_id = @job_id
order by step_id asc
set @job = @job + '
<br>
'
INSERT INTO #tmp (tmp) values (@job)
set @job = ''
FETCH NEXT FROM job_cursor INTO @job_id
END
CLOSE job_cursor
--커서 DEALLOCATE
DEALLOCATE job_cursor
select tmp from #tmp
drop table #tmp
GO
