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.

Serkan Arslan · Accepted Answer · 2017-09-25T08:51:51.610

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

edited Sep 25 '17 at 08:51

answered Sep 25 '17 at 08:20

Serkan Arslan

13,158
4
29
44

It works. I am new to all these DBA conecpts. If I want to get the Email Text also with the job how can I get it. – Manoj Nayak Sep 25 '17 at 09:20
1

You can get message text from `[dbo].[sysalerts]` table. – Serkan Arslan Sep 25 '17 at 10:27
@sarlan if I wan't to filter jobs belonging to a particular database then – Manoj Nayak Sep 25 '17 at 12:13
1

You can use `[sysjobsteps]`table for getting database info. For example `SELECT * FROM [dbo].[sysjobs] j INNER JOIN [dbo].[sysjobsteps] s ON j.job_id = s.job_id WHERE s.database_name ='master'` – Serkan Arslan Sep 25 '17 at 12:25

Get SQL Server Agent jobs configured with Email Notification

1 Answers1