Get a list of SQL Server Agent Jobs

Question

I've got an extensive selection of these to add to a spreadsheet and don't want to go through by hand. What it the T-SQL command(s) to generate a list of SQL Server Agent Jobs?

Answer 1

On each server, you can query the sysjobs table in the msdb. For instance:

SELECT job_id, [name] FROM msdb.dbo.sysjobs;

Answer 2

-- List of all the jobs currently running on server

SELECT job.job_id,notify_level_email
       ,name,enabled,description
       ,step_name,command,server,database_name

   FROM msdb.dbo.sysjobs job JOIN 
     msdb.dbo.sysjobsteps steps        
        ON job.job_id = steps.job_id

WHERE job.enabled = 1 -- remove this if you wish to return all jobs

Answer 3

This may be what you are looking for View (and Disable) SQL Agent Jobs with TSQL

Answer 4

My boss actually sorted out what I was after - this gave me the list I was after.

USE msdb SELECT name FROM sysjobs

Answer 5

Here is my contribution - also gets the category name and filters out the report server jobs.

SELECT  sysjobs.name 'Job Name',
        syscategories.name 'Category',
        CASE [description]
          WHEN 'No Description available.' THEN ''
          ELSE [description]
        END AS 'Description'
FROM    msdb.dbo.sysjobs
        INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
WHERE   syscategories.name <> 'Report Server'
ORDER BY sysjobs.name