Monday, June 20, 2016

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

Post a Comment

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...

Contributors