Getting the information that exists in "Job properties -> Notifications" page?

I use SQL Server 2008 R2 and I need get some properties from the msdb database.

I need a query to get information that exists in the Job properties -> Notifications page.

In which table I can find this information?

Because of where the information is stored in msdb, part of the information is obtained when the Job Properties window is initially opened, then more information is queried when you actually click on the Notifications page. SSMS uses an INSERT/EXEC on the sp_help_ stored procedures to obtain the operator information, which is pretty unwieldy... so unfortunately, using Profiler in this case to trap the queries isn't particularly helpful.

Here's a completed query that returns all the information from the page in one shot:

USE [msdb];
GO

WITH jobStates AS
(
    SELECT 0 AS Level,  'Disabled' AS Description UNION ALL
    SELECT 1,           'On Success' UNION ALL
    SELECT 2,           'On Failure' UNION ALL
    SELECT 3,           'On Completion'
)
SELECT
    j.job_id,
    j.name,
    es.Description AS EmailOnJobState, e.name AS EmailOperatorName, e.email_address AS EmailOperatorEmailAddress,
    ps.Description AS PageOnJobState, p.name AS PageOperatorName, p.pager_address AS PageOperatorPagerAddress,
    nss.Description AS NetSendOnJobState, ns.name AS NetSendOperatorName, ns.netsend_address AS NetSendOperatorNetSendAddress,
    els.Description AS EventLogOnJobState,
    ds.Description AS DeleteJobOnJobState
    FROM [dbo].[sysjobs] j
    INNER JOIN jobStates es ON es.Level = j.notify_level_email
    INNER JOIN jobStates ps ON ps.Level = j.notify_level_page
    INNER JOIN jobStates nss ON nss.Level = j.notify_level_netsend
    INNER JOIN jobStates els ON els.Level = j.notify_level_eventlog
    INNER JOIN jobStates ds ON ds.Level = j.delete_level
    LEFT OUTER JOIN [dbo].[sysoperators] e ON (j.notify_level_email > 0) AND (e.id = j.notify_email_operator_id)
    LEFT OUTER JOIN [dbo].[sysoperators] p ON (j.notify_level_page > 0) AND (p.id = j.notify_page_operator_id)
    LEFT OUTER JOIN [dbo].[sysoperators] ns ON (j.notify_level_netsend > 0) AND (ns.id = j.notify_netsend_operator_id);

StackExchange Database Administrators Q#47548, answer score: 4