Change the email across all Job alerts using script in SQL Server

As a DBA sometimes we get request for add/update/delete of DL’s – Distribution Lists (email groups) across all jobs on a SQL Server Instances. If the frequency of such request is very frequent then it’s better to write a script and run it on requests by simply modifying the email addresses.

Here is how we will do this:

We will take help of 2 tables  msdb.dbo.sysjobsteps and msdb.dbo.sysjobsteps

Query to retrive the list of jobs along with steps which satisfies the condition to search for an email (Simply update the “your-email-address-here” below and execute the query).

SELECT j.name, js.step_name, js.command FROM msdb.dbo.sysjobsteps js WITH(NOLOCK)
LEFT JOIN msdb.dbo.sysjobs j WITH(NOLOCK)
ON j.job_id = js.job_id  
WHERE js.command  like '%<your-email-address-here>%'


Update the old DL with new:

UPDATE msdb.dbo.sysjobsteps
SET command = REPLACE(command,'<search-email-group>','<new-email-group>')
WHERE command  like '%<your-search-term-here>%'