Updating Database Email Profile for all notifications I am creating an email profile for database email in a script that needs to be run on 200+ instances. The script will create a database email profile to send out alerts from agent jobs. Is there a way to go through all the jobs that exist on an instance and change a parameter so it will use the new email profile I created instead of the email profile its currently using? (added bonus if afterwards i can clean up all the unused email profiles)
DECLARE @sb INT;
DECLARE @sc INT;
SET @sb =
(
    SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
);
SET @sc =
(
    SELECT CONVERT(INT, value_in_use)
    FROM sys.configurations
    WHERE name = 'Database Mail XPs'
);
IF (@sb = 1 AND @sc = 1)
BEGIN
    PRINT 'Creating email profile';
    DECLARE @account_id INT;
    EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'DatabaseServices - SQLMail Account',                      
                                         @display_name = N'XXX - Database Services',                       
                                         @replyto_address = N'noreply@blah.com',                             
                                         @description = N'SQL Server generated email from databases services team', 
                                         @mailserver_name = 'yyy.xxx.pvt',                                  
                                         @email_address = 'ITDatabaseServices@texasmutual.com',
                                         @port = 25,                                                                
                                         @account_id = @account_id OUTPUT;                                          
END
I think what you're looking for is msdb.dbo.sysjobs and the notify_email_operator_id field specifically. If you can get the ID of the profile after creation, then you likely just need to update the table sysjobs references in a single UPDATE statement, on each instance you run your script on.