DECLARE @jobname VARCHAR(128),
@jobdescription VARCHAR(512),
@step_name VARCHAR(128),
@command VARCHAR(MAX),
@database VARCHAR(128),
@subsystem VARCHAR(128) = 'TSQL',
@WildChar BIT = 1
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
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
OR CHARINDEX(@jobname, j.name, 0) > 0)
AND (@jobdescription IS NULL
OR (@WildChar = 1
AND j.description LIKE @jobdescription)
OR (@WildChar = 0
AND CHARINDEX(@jobdescription, j.description, 0) > 0))
AND (@step_name IS NULL
OR (@WildChar = 1
AND st.step_name LIKE @step_name)
OR (@WildChar = 0
AND CHARINDEX(@step_name, st.step_name, 0) > 0))
AND (@command IS NULL
OR (@WildChar = 1
AND st.command LIKE @command)
OR (@WildChar = 0
AND CHARINDEX(@command, st.command, 0) > 0))
AND (@database IS NULL
OR (@WildChar = 1
AND st.database_name LIKE @database)
OR (@WildChar = 0
AND CHARINDEX(@database, st.database_name, 0) > 0))
AND (@subsystem IS NULL
OR (@WildChar = 1
AND st.subsystem LIKE @subsystem)
OR (@WildChar = 0
AND CHARINDEX(@subsystem, st.subsystem, 0) > 0))