DECLARE @jobname VARCHAR(128),
@jobdescription VARCHAR(512),
@step_name VARCHAR(128),
@command VARCHAR(MAX) = '%forfiles%del%',
@database VARCHAR(128),
@subsystem VARCHAR(128),
@ExecuteSQL BIT = 1,
@PrintSQL BIT = 1
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = Util.dbo.StringConcat(DISTINCT 'EXEC msdb.dbo.sp_update_job @job_name=N''' + REPLACE(j.name, '''', '''''') + ''',
@notify_level_netsend = 2,
@notify_level_page = 2,
@notify_email_operator_name = N''''
', '
')
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)
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
IF @ExecuteSQL = 1
AND @SQL <> ''
EXEC(@SQL)