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>%'
Posts
