Query to get the scheduled jobs detail

Few days before, I was asked a query by one of my friends to retrieve scheduled job details such as job name and the mail id to which the job has configured.  
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,