Last active
February 17, 2024 22:49
-
-
Save petesql/01960a4c2c9677aeb585f137fa3f359c to your computer and use it in GitHub Desktop.
Get all SQL Server Agent Jobs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- List all SQL Agent Jobs | |
SELECT | |
s.name AS JobName, | |
dp.name AS JobOwner, | |
sc.name AS JobCategory, | |
s.description AS JobDescription, | |
CASE s.enabled | |
WHEN 1 THEN 'Yes' | |
WHEN 0 THEN 'No' | |
END AS IsEnabled, | |
s.date_created AS JobCreatedOn, | |
s.date_modified AS JobLastModifiedOn, | |
sv.name AS ServerName, | |
CASE | |
WHEN sch.schedule_uid IS NULL THEN 'No' | |
ELSE 'Yes' | |
END AS IsScheduled, | |
sch.name AS JobScheduleName, | |
TRY_CONVERT(datetime, CONCAT(js.last_run_date, ' ', STUFF(STUFF(RIGHT('000000' + CAST(js.last_run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':'))) AS LastRunDateTime | |
FROM | |
msdb.dbo.sysjobs AS s | |
LEFT JOIN msdb.sys.servers AS sv | |
ON s.originating_server_id = sv.server_id | |
LEFT JOIN msdb.dbo.syscategories AS sc | |
ON s.category_id = sc.category_id | |
LEFT JOIN msdb.dbo.sysjobsteps AS js | |
ON s.job_id = js.job_id | |
AND s.start_step_id = js.step_id | |
LEFT JOIN msdb.sys.database_principals AS dp | |
ON s.owner_sid = dp.sid | |
LEFT JOIN msdb.dbo.sysjobschedules AS sJOBSCH | |
ON s.job_id = sJOBSCH.job_id | |
LEFT JOIN msdb.dbo.sysschedules AS sch | |
ON sJOBSCH.schedule_id = sch.schedule_id | |
ORDER BY | |
JobName; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment