sql server search job commands

 

Handy query to search a particular command across all the Jobs.

To search all the jobs which are using mail object , set the @keyword value to “Mail”
To search all the jobs which are using external folders , set the @keyword value to “\”
To search all the jobs which are calling DTS packages , set the @keyword value to “.dts”

declare @keyword varchar(max) set @keyword = 'mail' set @keyword = '%'+@keyword +'%' SELECT [sysjobs].[name] AS N'job_name',[sysjobsteps].[step_name],[sysjobsteps].[command] AS N'step_command',[sysjobsteps].[database_name],[sysjobsteps].[output_file_name],[sysjobsteps].[last_run_date] FROM [msdb].[dbo].[sysjobsteps]INNER JOIN [msdb].[dbo].[sysjobs]ON [msdb].[dbo].[sysjobsteps].[job_id] = [msdb].[dbo].[sysjobs].[job_id] where [sysjobsteps].[command] like @keyword