SQLServer调用存储过程生成已有job的创建脚本

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_DumpJobsql]
(
@a_JobName NVARCHAR(30) = ''
)
AS
BEGIN

/********************************************************************
* 过程说明: 只适用于不带邮件、报警的作业
* 需要参数:
* @target_loginname 为需要创建的服务器的登录名
* @i_enabled 为是否执行( 0=禁用 ,1=启用 )
*
*
* 调用示例(需要以文本格式显示执行结果):
* 生成单个：EXEC [master].[dbo].[usp_DumpJobsql] 'UpLoadNewFiles'
* 生成所有：EXEC [master].[dbo].[usp_DumpJobsql]
********************************************************************/

SET NOCOUNT ON
DECLARE @i_enabled TINYINT
DECLARE @sql NVARCHAR(MAX)
DECLARE @i_job_name NVARCHAR(1000)
DECLARE @i_notify_level_eventlog INT
DECLARE @i_notify_level_email INT
DECLARE @i_notify_level_netsend INT
DECLARE @i_notify_level_page INT
DECLARE @i_delete_level INT
DECLARE @i_description NVARCHAR(1000)
DECLARE @i_category_name NVARCHAR(1000)
DECLARE @i_owner_login_name NVARCHAR(1000)
DECLARE @i_category_class INT
DECLARE @i_start_step_id INT
DECLARE @i_step_name NVARCHAR(1000)
DECLARE @i_step_id INT
DECLARE @i_cmdexec_success_code INT
DECLARE @i_on_success_action INT
DECLARE @i_on_success_step_id INT
DECLARE @i_on_fail_action INT
DECLARE @i_on_fail_step_id INT
DECLARE @i_retry_attempts BIGINT
DECLARE @i_retry_interval INT
DECLARE @i_os_run_priority INT
DECLARE @i_subsystem NVARCHAR(1000)
DECLARE @i_command NVARCHAR(4000)
DECLARE @i_database_name NVARCHAR(100)
DECLARE @i_flags INT
DECLARE @i_class NVARCHAR(10)
DECLARE @i_type NVARCHAR(10)
DECLARE @c_jobid UNIQUEIDENTIFIER
DECLARE @c_categoryid INT
DECLARE @loop_stepid INT
DECLARE @m_stepid INT
DECLARE @loop_scheduleid INT
DECLARE @m_scheduleid INT
DECLARE @i_schedule_enabled TINYINT
DECLARE @i_freq_type INT
DECLARE @i_schedule_name NVARCHAR(1000)
DECLARE @i_freq_interval INT
DECLARE @i_freq_subday_type INT
DECLARE @i_freq_subday_interval INT
DECLARE @i_freq_relative_interval INT
DECLARE @i_freq_recurrence_factor INT
DECLARE @i_active_start_date BIGINT
DECLARE @i_active_end_date BIGINT
DECLARE @i_active_start_time BIGINT
DECLARE @i_active_end_time BIGINT
DECLARE @i_schedule_uid NVARCHAR(1000)

SET @i_class = 'JOB'
SET @i_type = 'LOCAL'

DECLARE job CURSOR FOR
SELECT a.job_id ,a.category_id FROM msdb.dbo.sysjobs a , msdb.dbo.syscategories c
WHERE a.category_id = c.category_id
AND c.name NOT LIKE '%Database Maintenance%'
AND c.name NOT LIKE '%REPL%'
AND c.name <> 'Log Shipping'
AND a.name <> 'syspolicy_purge_history'
AND (@a_JobName = '' OR a.name = @a_JobName)
OPEN job
FETCH job INTO @c_jobid ,@c_categoryid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ''
SELECT @i_job_name = a.name ,
@i_enabled = [enabled] ,
@i_notify_level_eventlog = notify_level_eventlog ,
@i_notify_level_email = notify_level_email ,
@i_notify_level_netsend = notify_level_netsend ,
@i_notify_level_page = notify_level_page ,
@i_delete_level = delete_level ,
@i_description = [description] ,
@i_category_name = c.name ,
@i_owner_login_name = ISNULL(SUSER_SNAME(a.owner_sid), N'''') ,
@i_category_class = category_class
FROM msdb.dbo.sysjobs a ,msdb.dbo.syscategories c
WHERE a.category_id=c.category_id AND a.job_id=@c_jobid AND a.category_id = @c_categoryid

SET @sql=@sql+CHAR(13)+CHAR(10) + 'USE [msdb]'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'GO'
SET @sql=@sql+CHAR(13)+CHAR(10) + '/****** Object: Job ['+ @i_job_name +'] Script Date: '+CONVERT(NVARCHAR,GETDATE(),22)+' ******/'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'BEGIN TRANSACTION'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'DECLARE @ReturnCode INT'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'SELECT @ReturnCode = 0'
SET @sql=@sql+CHAR(13)+CHAR(10) + '/****** Object: JobCategory ['+ @i_category_name +'] Script Date: 08/20/2012 12:35:16 ******/'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'''+ @i_category_name +''' AND category_class='+ CAST(@i_category_class AS NVARCHAR) +' )'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'BEGIN'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'''+ @i_class +''', @type=N'''+ @i_type +''', @name=N'''+ @i_category_name +''''
SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
SET @sql=@sql+CHAR(13)+CHAR(10) + ''
SET @sql=@sql+CHAR(13)+CHAR(10) + 'END'
SET @sql=@sql+CHAR(13)+CHAR(10) + ''
SET @sql=@sql+CHAR(13)+CHAR(10) + 'DECLARE @jobId BINARY(16)'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'''+ @i_job_name +''','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @enabled='+ CAST(@i_enabled AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @notify_level_eventlog='+ CAST(@i_notify_level_eventlog AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @notify_level_email='+ CAST(@i_notify_level_email AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @notify_level_netsend='+ CAST(@i_notify_level_netsend AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @notify_level_page='+ CAST(@i_notify_level_page AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @delete_level='+ CAST(@i_delete_level AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @description=N'''+ @i_description +''','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @category_name=N'''+ @i_category_name +''','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @owner_login_name=N'''+ @i_owner_login_name +''', @job_id = @jobId OUTPUT'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
IF EXISTS ( SELECT TOP 1 1 FROM msdb.dbo.sysjobsteps WHERE job_id = @c_jobid )
BEGIN
SELECT @loop_stepid = MIN(step_id) ,@m_stepid = MAX(step_id) FROM msdb.dbo.sysjobsteps WHERE job_id = @c_jobid
WHILE (@loop_stepid < = @m_stepid)
BEGIN
SELECT @i_start_step_id = start_step_id,
@i_step_name = step_name ,
@i_step_id = step_id,
@i_cmdexec_success_code = cmdexec_success_code ,
@i_on_success_action = on_success_action ,
@i_on_success_step_id = on_success_step_id ,
@i_on_fail_action = on_fail_action ,
@i_on_fail_step_id = on_fail_step_id ,
@i_retry_attempts = retry_attempts ,
@i_retry_interval = retry_interval ,
@i_os_run_priority = os_run_priority ,
@i_subsystem = subsystem ,
@i_command = command ,
@i_database_name = database_name ,
@i_flags = flags
FROM msdb.dbo.sysjobs a ,msdb.dbo.sysjobsteps b
WHERE a.job_id = b.job_id AND step_id = @loop_stepid AND a.job_id = @c_jobid

SET @sql=@sql+CHAR(13)+CHAR(10) + '/****** Object: Step ['+ @i_step_name +'] Script Date: '+CONVERT(NVARCHAR,GETDATE(),22)+' ******/'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'''+ @i_step_name +''','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @step_id='+ CAST(@i_step_id AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @cmdexec_success_code='+ CAST(@i_cmdexec_success_code AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @on_success_action='+ CAST(@i_on_success_action AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @on_success_step_id='+ CAST(@i_on_success_step_id AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @on_fail_action='+ CAST(@i_on_fail_action AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @on_fail_step_id='+ CAST(@i_on_fail_step_id AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @retry_attempts='+ CAST(@i_retry_attempts AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @retry_interval='+ CAST(@i_retry_interval AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @os_run_priority='+ CAST(@i_os_run_priority AS NVARCHAR) +', @subsystem=N'''+ @i_subsystem +''','
SET @sql=@sql+CHAR(13)+CHAR(10) + ISNULL(' @command=N''' + REPLACE(@i_command ,'''' ,'''''') + ''',' ,'')
SET @sql=@sql+CHAR(13)+CHAR(10) + ISNULL(' @database_name=N'''+ @i_database_name +''',' ,'')
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @flags='+ CAST(@i_flags AS NVARCHAR)
SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

SET @loop_stepid = ( SELECT TOP 1 step_id FROM msdb.dbo.sysjobsteps WHERE job_id = @c_jobid AND step_id > @loop_stepid ORDER BY step_id )
END
END

SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = '+ CAST(@i_start_step_id AS NVARCHAR)
SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
IF EXISTS ( SELECT TOP 1 1 FROM msdb.dbo.sysschedules c ,msdb.dbo.sysjobschedules d WHERE c.schedule_id = d.schedule_id AND job_id = @c_jobid )
BEGIN
SELECT @loop_scheduleid= MIN(c.schedule_id) ,@m_scheduleid = MAX(c.schedule_id)
FROM msdb.dbo.sysschedules c ,msdb.dbo.sysjobschedules d
WHERE c.schedule_id = d.schedule_id AND job_id = @c_jobid
WHILE ( @loop_scheduleid <= @m_scheduleid )
BEGIN
SELECT @i_schedule_enabled = [enabled] ,
@i_freq_type = freq_type ,
@i_schedule_name = name,
@i_freq_interval = freq_interval ,
@i_freq_subday_type = freq_subday_type ,
@i_freq_subday_interval = freq_subday_interval ,
@i_freq_relative_interval = freq_relative_interval ,
@i_freq_recurrence_factor = freq_recurrence_factor ,
@i_active_start_date = active_start_date ,
@i_active_end_date = active_end_date ,
@i_active_start_time = active_start_time ,
@i_active_end_time = active_end_time ,
@i_schedule_uid = schedule_uid
FROM msdb.dbo.sysschedules c LEFT JOIN msdb.dbo.sysjobschedules d
ON c.schedule_id = d.schedule_id
WHERE d.job_id = @c_jobid AND c.schedule_id = @loop_scheduleid

SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'''+ @i_schedule_name +''','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @enabled='+ CAST(@i_schedule_enabled AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_type='+ CAST(@i_freq_type AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_interval='+ CAST(@i_freq_interval AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_subday_type='+ CAST(@i_freq_subday_type AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_subday_interval='+ CAST(@i_freq_subday_interval AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_relative_interval='+ CAST(@i_freq_relative_interval AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @freq_recurrence_factor='+ CAST(@i_freq_recurrence_factor AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @active_start_date='+ CAST(@i_active_start_date AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @active_end_date='+ CAST(@i_active_end_date AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @active_start_time='+ CAST(@i_active_start_time AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @active_end_time='+ CAST(@i_active_end_time AS NVARCHAR) +','
SET @sql=@sql+CHAR(13)+CHAR(10) + ' @schedule_uid=N'''+ @i_schedule_uid +''''
SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'

SET @loop_scheduleid = ( SELECT TOP 1 c.schedule_id FROM msdb.dbo.sysschedules c ,msdb.dbo.sysjobschedules d
WHERE c.schedule_id = d.schedule_id AND job_id = @c_jobid AND c.schedule_id > @loop_scheduleid )
END
END

SET @sql=@sql+CHAR(13)+CHAR(10) + 'EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N''(local)'''
SET @sql=@sql+CHAR(13)+CHAR(10) + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'COMMIT TRANSACTION'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'GOTO EndSave'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'QuitWithRollback:'
SET @sql=@sql+CHAR(13)+CHAR(10) + ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION'
SET @sql=@sql+CHAR(13)+CHAR(10) + 'EndSave:'
SET @sql=@sql+CHAR(13)+CHAR(10) + ''
SET @sql=@sql+CHAR(13)+CHAR(10) + 'GO'

PRINT @sql
SELECT LEN(@sql)
FETCH NEXT FROM job INTO @c_jobid ,@c_categoryid
END
CLOSE job
DEALLOCATE job

END

GO