DECLARE @jobname VARCHAR(128),
@jobdescription VARCHAR(512),
@step_name VARCHAR(128),
@command VARCHAR(MAX) = '%xp_restore_database%',
@database VARCHAR(128),
@subsystem VARCHAR(128) = 'TSQL'
DECLARE @sql VARCHAR(MAX)
--SELECT j.name AS JobName,
-- j.enabled,
-- j.description,
-- j.start_step_id,
-- st.step_id,
-- st.step_name,
-- st.subsystem,
-- JobSubsystemList,
-- st.command,
-- st.database_name,
-- st.last_run_date,
-- st.last_run_time
--SELECT @sql = Util.dbo.StringConcat('EXEC msdb.dbo.sp_update_jobstep @job_id=N''' + CAST(j.job_id AS VARCHAR(128)) + ''', @step_id=1 , @subsystem=N''CmdExec'',
-- @command=N''C:\apps\Applications\dtlogexec.exe /F "C:\Apps\CoreStore\Packages\DW_MATERIALIZATION.dtsx"'',
-- @flags=32','
--GO
--')
SELECT @sql = Util.dbo.StringConcat('UPDATE msdb.dbo.sysjobsteps
SET Command = ''' + REPLACE(st.command, '''', '''''') + '''
WHERE job_id = ''' + CAST(j.job_id AS VARCHAR(128)) + '''
AND step_id = ' + CAST (st.step_id AS VARCHAR) + '
GO
', '')
FROM msdb.dbo.sysjobsteps st (NOLOCK)
INNER JOIN msdb.dbo.sysjobs AS j (NOLOCK) ON j.job_id = st.job_id
CROSS APPLY (SELECT CAST(SUM(Counter) AS VARCHAR) + '-' + Util.dbo.StringConcat(subsystem + ' ('
+ CAST(counter AS VARCHAR) + ')',
', ') JobSubsystemList
FROM (SELECT s2.subsystem,
COUNT(*) AS Counter
FROM msdb.dbo.sysjobsteps s2 (NOLOCK)
WHERE s2.Job_id = st.job_id
GROUP BY s2.subsystem) b) k
WHERE (@jobname IS NULL
OR j.name LIKE @jobname)
AND (@jobdescription IS NULL
OR j.description LIKE @jobdescription)
AND (@step_name IS NULL
OR st.step_name LIKE @step_name)
AND (@command IS NULL
OR st.command LIKE @command)
AND (@database IS NULL
OR st.database_name LIKE @database)
AND (@command IS NULL
OR st.command LIKE @command)
AND (@subsystem IS NULL
OR st.subsystem LIKE @subsystem)
EXEC Util.dbo.PrintLargeText
@sql