List of Jobs that Run

Transact-SQL
1

Hi,

I am looking for a SQL query that pulls all jobs that run through out the day. I want it to include The Job_name and step_name. I don't want to include jobs that are disabled.

If someone can help me with this, that would be great!

Thank you so much.

Wendy

2

Hi,
A very simple try will be below

select * from sysjobs
where enabled = 1
order by [name]

However, if you have any job that does not have any schedule, or the schedule within the job got disabled, then will need to link to other tables to look that up.

Hope this helps

3

Here is overkill of the question. Please notice I use a three part name

SELECT 
     @@SERVERNAME AS JobServer 
    , Jobs.[name] AS JobName 
    , Steps.step_id as StepNumber 
    , Steps.step_name as StepName 
    , CAST(CAST(Schedules.next_run_date AS VARCHAR(10)) AS DATE) AS NextRunDate
    -- Normalize Schedules.next_run_time to six characters (leading zeros) then grab need part
    , CAST(LEFT(RIGHT('000000' + CAST(Schedules.next_run_time AS VARCHAR(6)), 6), 2) + ':' 
        + SUBSTRING(RIGHT('000000' + CAST(Schedules.next_run_time AS VARCHAR(6)), 6), 3, 2) + ':' 
        + RIGHT(RIGHT('000000' + CAST(Schedules.next_run_time AS VARCHAR(6)), 6), 2) AS TIME(0)) 
        AS NextRunTime 
    , SysSchedules.[name] AS SchdName 
    , Steps.command 
FROM msdb.dbo.sysjobs                          Jobs 
RIGHT OUTER JOIN msdb.dbo.sysjobschedules Schedules ON Jobs.Job_ID = Schedules.Job_ID
LEFT OUTER JOIN msdb.dbo.sysjobsteps          Steps ON Schedules.Job_ID = Steps.Job_ID 
INNER JOIN msdb.dbo.sysschedules       SysSchedules ON Schedules.schedule_id = SysSchedules.schedule_id
WHERE Jobs.enabled = 1 
ORDER BY JobName, StepNumber