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.

Script to rename SQL Agent Jobs

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:

Rename SQL Agent Jobs

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:

Query to rename SQL Agent 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

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/rename-sqlagnetjobs/
Twitter
PINTEREST
LinkedIn