Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

up vote 26 down vote accepted

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

SELECT job_id, [name] FROM msdb.dbo.sysjobs;
share|improve this answer
    
Thanks, I think you just beat me to the punch so you get the prize! –  alimack May 29 '09 at 14:50

-- 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
share|improve this answer
    
Can you format the SQL? –  Frank V Oct 26 '09 at 19:16

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

share|improve this answer
    
Thanks for the prompt reply, I couldn't actually get it working. I assume this code is SQL Server 2005 and up? –  alimack May 29 '09 at 14:00
    
This is the problem with "answering" a question with little more than a link. The link appears to no longer be valid and thus the "answer" is now pretty much useless... –  mutex Jun 25 at 3:24

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 
share|improve this answer

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

USE msdb SELECT name FROM sysjobs

share|improve this answer

protected by splattne May 31 '11 at 13:10

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.