Searching for Keywords in SQL Server Jobs

Hi friends all around the globe, it’s been a while since my last article but this is a very busy period with many other community activities of which you will soon enjoy the outcome! These activities have to do with heavy software development as well as the authoring of my third eBook with title “Developing SQL Server“. More updates on everything will be provided along the way. In this article, we will talk about searching for keywords in SQL Server jobs.

 

What does this Article Discuss

This article is actually a T-SQL tip that can become quite handy. It tackles the issue of finding which SQL Server Agent jobs are affected due to schema changes (i.e. table rename, etc.).

 


Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access / Live Demos / Downloadable Resources and more!)

Learn More


A Simple Example on How you can Search for Keywords in SQL Server Jobs

Consider the following example where you have three tables:
-table1
-table2
-table3

You also have a SQL Server Agent jobs that reference the above tables.

Then, for any reason, you want to rename ‘table2‘ to ‘table2New

What happens to the SQL Server Agent job the next time it runs? Of course it fails.

When you rename an object in SQL Server using the sp_rename stored procedure, the Database Engine warns you with the below message:
Caution: Changing any part of an object name could break scripts and stored procedures.

In order not to get into problems with such actions, especially in the case of large databases, you must first research if there are any functions, stored procedures, jobs, etc. that might be affected.

The below script will return all jobs that include one or more steps that reference a given string pattern (this can be a table name, stored procedure name, function name, etc.). In this case we search for ‘table2’:

declare @pattern nvarchar(max)
set @pattern='table2'

select j.[job_id],j.[name],j.[enabled],j.[description],s.command,j.[date_created],j.[date_modified]
from msdb.dbo.sysjobs j
inner join msdb.dbo.[sysjobsteps] s on j.job_id=s.job_id 
where s.command like '%'+@pattern+'%'

A subsequent article will contain a master procedure that can be used for searching for a given pattern in all the programmability objects in SQL Server.

 

Featured Online Courses:

 

Check our Related SQL Server Administration Articles:

 

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub