Get SQL Agent Jobs with Job Steps

get-sql-agent-jobs-with-job-steps.sql

-- Get all SQL Agent Jobs with Job Steps
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
    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;