List email notification recipients for Jobs

This SQL script lists the names and addresses of all Notification Recipients used by Notification Actions in Responses defined for Jobs and Job Steps and for Notification Tasks within jobs.

select j.name as JobName,0 as stepNumber,'Response' as [Type], nr.name,addr.address

from job j 
    join response r on j.oid=r.JobOID 
        join ResponseAction  a on a.ResponseOID=r.oid 
            join notificationAction na on na.oid=a.oid
                join NotificationActionRecipient nar on nar.NotificationActionOID=na.oid
                join NotificationRecipient nr on nr.oid=nar.NotificationRecipientOID
                join notificationAddress addr on addr.ownerOID=nr.oid


union 

select j.name as JobName,js.stepNumber as stepNumber,'Response' as [Type], nr.name,addr.address

from job j 
join jobStep js on j.oid=js.JobOID 
    join response r on js.oid=r.JobStepOID 
        join ResponseAction  a on a.ResponseOID=r.oid 
            join notificationAction na on na.oid=a.oid
                join NotificationActionRecipient nar on nar.NotificationActionOID=na.oid
                join NotificationRecipient nr on nr.oid=nar.NotificationRecipientOID
                join notificationAddress addr on addr.ownerOID=nr.oid

union

select j.name as JobName,js.stepNumber as stepNumber,'Notification Task' as [Type], nr.name,addr.address

from job j 
join jobStep js on j.oid=js.JobOID 
join task t on js.oid=t.JobStepOID 
join notificationTask nt on nt.oid=t.oid
join NotificationTaskRecipient ntr on ntr.NotificationTaskOID=t.oid
                join NotificationRecipient nr on nr.oid=ntr.NotificationRecipientOID
                join notificationAddress addr on addr.ownerOID=nr.oid

order by j.name,stepNumber