This article is about a script to Rename SQL Agent Jobs. First of all let me tell you the requirement.
The environment had a dedicated database for DBA’s which is used for several database maintenance and hence had a bunch of SQL Agent jobs associated. I am sure some of you also have similar setup.
Project team came up with an updated version. As a result in the upgrade process we had to rename all the jobs. I am sure you don’t wanna do it manually for each instance. Especially if you have more than 100 instances 🙂
The below queries will help in following conditions:
- Rename SQL Agent Jobs having particular naming convention
- Change the name of SQL Agent Jobs by removing first few characters
Note: The queries can not be fully generic and needs to be customized as per your environment. Basically depends upon the job name.
Query to Rename SQL Agent Jobs having particular naming conventions:
The below example shows job names having names like ‘1_DBA job Name’, ‘2_DBA job Name’, to ‘9_DBA job Name’. Please make the necessary changes as per your environment as provided in the comments.
DECLARE @SQLStatement VARCHAR(200) DECLARE @jobName SYSNAME DECLARE c1 CURSOR FOR SELECT name FROM msdb.dbo.sysjobs_view WHERE name LIKE '%_DBA%' --Change the LIKE operator OPEN c1 FETCH NEXT FROM c1 INTO @jobName IF @@CURSOR_ROWS=0 PRINT 'No Job found! Please re-check LIKE operator.' WHILE @@fetch_status=0 BEGIN SET @SQLStatement= 'EXEC msdb.dbo.sp_update_job @job_name =''' + @jobName + ''', @new_name =''OLD_' +@jobName +'''' PRINT(@SQLStatement) --EXEC (@SQLStatement) --Uncomment to Execute FETCH NEXT FROM c1 INTO @jobName END CLOSE c1 DEALLOCATE c1
OutPut will produce set of statements:
The result set is for you to verify the way you want to rename the jobs. Once verified either you execute the statements Or you can uncomment the statement EXEC (@SQLStatement) in the above code and execute.
Screenshot showing the renamed jobs:
Rollback query for the above changes:
The same script can be taken as reference to rename SQL Agent Jobs by removing first few characters.
DECLARE @SQLStatement VARCHAR(200) DECLARE @jobName SYSNAME DECLARE @jobName1 SYSNAME DECLARE c1 CURSOR FOR SELECT name FROM msdb.dbo.sysjobs_view WHERE name LIKE 'OLD_%' OPEN c1 FETCH next FROM c1 INTO @jobName IF @@CURSOR_ROWS=0 PRINT 'No Job found! Please re-check LIKE operator.' WHILE @@fetch_status=0 BEGIN SELECT @jobName1 = SUBSTRING(@jobName,5,LEN(@jobName)) --Second parameter may need change as per the job name SET @SQLStatement= 'EXEC msdb.dbo.sp_update_job @job_name =''' + @jobName + ''', @new_name =''' +@jobName1 +'''' --PRINT(@SQLStatement) EXEC (@SQLStatement) FETCH NEXT FROM c1 INTO @jobName END CLOSE c1 DEALLOCATE c1
Hope this is helpful. Furthermore you are most welcome to tweak the queries as per your requirement. I am open to feedback therefore please feel free to comment.
In addition you may also like the below queries:
List all permissions for a user in all or selective databases
Script to List All Users with db_owner role in SQL 2000
Script to find the SQL Agent Job Name in Execution
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name
