Rename SQL Agent Operator and all its Notifications

This script will change an Operator's name and update all three notification option types the Operator has.

-- Will change from the old Operator to the new Operator for all 
-- SQL Agent Alerts and Jobs for all three notification options. 
use msdb
set xact_abort on

-- Set these variables to the old and new Operator of your choice. 
-- This is the only code you have to change. 
declare @OperatorNameOld sysname
declare @OperatorNameNew sysname
set @OperatorNameOld = 'My Old Operator Name'
set @OperatorNameNew = 'My New Operator Name'

-- Verify your old operator exists. 
if not exists (select * from sysoperators where name = @OperatorNameOld)
    raiserror('   *** @OperatorNameOld ''%s'' does not exist!', 20, 1, @OperatorNameOld) with nowait, log

print 'Old operator name is ''' + @OperatorNameOld + ''' '

-- Verify your new operator exists. 
if not exists (select * from sysoperators where name = @OperatorNameNew)
    raiserror('   *** @OperatorNameNew ''%s'' does not exist!', 20, 1, @OperatorNameNew) with nowait, log

print 'New operator name is ''' + @OperatorNameNew + ''' '

-- Iterate through the Alerts and change the operator. 
declare @AlertNameVar            sysname
declare @IncludeEventDescription tinyint

declare cur cursor for
    select sa.name                        as AlertName,
           so.name                        as OperatorNameOld,
           sa.include_event_description   as IncludeEventDescription
      from sysalerts          sa
      join sysnotifications   sn
        on sa.id = sn.alert_id
      join sysoperators       so
        on sn.operator_id = so.id
     where so.name = @OperatorNameOld

open cur
fetch next from cur into @AlertNameVar, @OperatorNameOld, @IncludeEventDescription

while @@fetch_status = 0
begin
    print 'Alert ''' + @AlertNameVar + ''' - adding new operator ''' + @OperatorNameNew + ''' '

    exec sp_add_notification
        @alert_name          = @AlertNameVar,
        @operator_name       = @OperatorNameNew,
        @notification_method = @IncludeEventDescription

    print 'Alert ''' + @AlertNameVar + ''' - deleting old operator ''' + @OperatorNameOld + ''' '

    exec sp_delete_notification
        @alert_name    = @AlertNameVar,
        @operator_name = @OperatorNameOld

    fetch next from cur into @AlertNameVar, @OperatorNameOld, @IncludeEventDescription
end

close cur
deallocate cur

-- Iterate through the Jobs and change the operator 
-- for each of the three kinds of notifications. 
declare @JobNameVar             sysname
declare @OperatorNameMailOld    sysname
declare @OperatorNamePageOld    sysname
declare @OperatorNameNetSendOld sysname

declare cur cursor for
    select sj.name          as JobName,
           soMail.name      as OperatorNameMailOld,
           soPage.name      as OperatorNamePageOld,
           soNetSend.name   as OperatorNameNetSendOld
      from sysjobs        sj
 left join sysoperators   soMail
        on sj.notify_email_operator_id = soMail.id
 left join sysoperators   soPage
        on sj.notify_page_operator_id = soPage.id
 left join sysoperators   soNetSend
        on sj.notify_netsend_operator_id = soNetSend.id
     where (soMail.name    = @OperatorNameOld
        or  soPage.name    = @OperatorNameOld
        or  soNetSend.name = @OperatorNameOld)

open cur
fetch next from cur into @JobNameVar, @OperatorNameMailOld, @OperatorNamePageOld, @OperatorNameNetSendOld

while @@fetch_status = 0
begin
    -- Mail Job notification. 
    if @OperatorNameMailOld is not null
    begin
        print 'Job ''' + @JobNameVar + ''' - changing Mail operator name to ''' + @OperatorNameNew + ''' '

        exec msdb.dbo.sp_update_job 
            @job_name                   = @JobNameVar, 
            @notify_email_operator_name = @OperatorNameNew
    end

    -- Page Job notification. 
    if @OperatorNamePageOld is not null
    begin
        print 'Job ''' + @JobNameVar + ''' - changing Page operator name to ''' + @OperatorNameNew + ''' '

        exec msdb.dbo.sp_update_job 
            @job_name                  = @JobNameVar, 
            @notify_page_operator_name = @OperatorNameNew
    end

    -- NetSend Job notification. 
    if @OperatorNameNetSendOld is not null
    begin
        print 'Job ''' + @JobNameVar + ''' - changing NetSend operator name to ''' + @OperatorNameNew + ''' '

        exec msdb.dbo.sp_update_job 
            @job_name                     = @JobNameVar, 
            @notify_netsend_operator_name = @OperatorNameNew
    end

    fetch next from cur into @JobNameVar, @OperatorNameMailOld, @OperatorNamePageOld, @OperatorNameNetSendOld
end

close cur
deallocate cur

-- Delete old operator. 
exec msdb.dbo.sp_delete_operator @name = @OperatorNameOld
print 'Dropped old operator name ''' + @OperatorNameOld + ''' '

go