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.