How to search SQL Server Agent Command Text for a string

13 May,2020 by Jack Vamvas

Question: I'd like to find all SQL Server Agent Jobs with a certain string within the Command section . There's been a change in reference to a database table, therefore I need to review all SQL Server Agent Jobs and check they are not referencing this database table. 

Is there a sql query I can use to search for the string?  As an extra request - do you have any advice  on how to run this query across multiple SQL Server Instances and create a report?

Answer: Both of these tasks are possible . Firstly , let's look at t-sql query to extract . This query will check the msdb system database for the string  used by the LIKE predicator . Replace  'mystring' with whatever term you require. 

 

SELECT s.step_id as 'Step ID',
       j.[name] as 'SQL Agent Job Name',
       s.database_name as 'DB Name',
       s.command as 'Command'
FROM   msdb.dbo.sysjobsteps AS s
INNER JOIN msdb.dbo.sysjobs AS j ON  s.job_id = j.job_id
WHERE  s.command LIKE '%mystring%'


Executing this query on a few SQL Server Instances is not very time consuming . But what if you need to execute this script across hundreds or thousands of SQL Server Instances. This is where I find using Powershell a very powerful method to iterate, extract and create reports .

There are different approaches within Powershell to report on multiple servers. Read Powershell - run script on all sql servers   for a framework on how to run any query on multiple SQL Servers.    It is important to be careful when running queries across multiple servers. Maintaining a solid sql security policy    will minimise the possibility of mistakes.  

Read more on sql server agent jobs

Send sql server agent job history through email notification

List enabled status of SQL Server Agent Jobs (SQL Server DBA)


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to search SQL Server Agent Command Text for a string


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer