It is very easy to get the details using sysjobs, sysoperators and syscategories system tables. Here is the script I created to list the jobs name and email id with some extra columns such as job owner, category, enabled and email notification.
SELECT [Job Name] = j.[name] ,
[Owner] = SUSER_SNAME(j.[owner_sid]),
[Category] = c.[name ] ,
[Email id] = o.[email_address] ,
[Enabled] = j.[enabled ] ,
[Email Notification] =
CASE j.[notify_level_email]
WHEN 0
THEN 'Never'
WHEN 1
THEN 'When the job succeeds'
WHEN 2
THEN 'When the job fails'
WHEN 3
THEN 'When the job completes (regardless of the job outcome)'
ELSE 'UNKNOWN'
END
FROM msdb.dbo.[sysjobs] j
LEFT OUTER JOIN msdb.dbo.[sysoperators] o
ON j.[notify_email_operator_id] = o.[id]
LEFT OUTER JOIN msdb.dbo.[syscategories] C
ON j.[category_id] = c.[category_id]
WHERE j.[enabled] = 1
ORDER BY j.[name]
Using this script you can retrieve the details for all the enabled jobs. If you want to get disabled jobs too, just eliminate the ‘where’ clause in the script and try.:-)
Then the output will looks like this,
