Get SQL Server Agent jobs configured with Email Notification

Question

I have lot of jobs in my SQL server agent. I want to know which all the jobs are configured with email notification. I can do individually by editing and checking for the job steps. Any query/procedure to give the job names that have email notification in one shot.

Answer 1

You can run this query on [msdb] db.

use [msdb]

select j.name, o.name, o.email_address 
from 
[dbo].[sysjobs] j 
inner join [dbo].[sysoperators] o on j.notify_email_operator_id  = o.id