Wednesday, February 18, 2015

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.

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.).

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.

[Ad]
Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: