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?
Asked
Active
Viewed 2.6e+01k times
4 Answers
73
On each server, you can query the sysjobs table in the msdb. For instance:
SELECT job_id, [name] FROM msdb.dbo.sysjobs;
K. Brian Kelley
- 9,004
- 31
- 33
-
Thanks, I think you just beat me to the punch so you get the prize! – alimack May 29 '09 at 14:50
25
-- 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
INNER 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
robyaw
- 115
- 7
6
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
Sam
- 1,990
- 1
- 14
- 21
2
My boss actually sorted out what I was after - this gave me the list I was after.
USE msdb SELECT name FROM sysjobs
alimack
- 933
- 2
- 11
- 21