SQL – Getting list of jobs and their steps

How to get list of jobs an their steps. For this I will use three tables dbo.sysjobs, dbo.sysjobsteps and dbo.sysjobschedules in msdb database.

You can create select from each of these tables to get relevant data.

select name,enabled,description from msdb.dbo.sysjobs
select job_id,step_id,step_name,command,database_name from msdb.dbo.sysjobsteps
select job_id,next_run_date,next_run_time from msdb.dbo.sysjobschedules

 

But we can combine these three tables, to get unified complex select.

SELECT
    job.name,
    job.enabled,
    job.description,
    steps.step_name,
    steps.command,
    steps.database_name,
    sch.next_run_date AS ‘Sched Date’,
    sch.next_run_time AS ‘Sched Time’
FROM msdb.dbo.sysjobs job JOIN
     msdb.dbo.sysjobsteps steps       
        ON job.job_id = steps.job_id INNER JOIN
    msdb.dbo.sysjobschedules AS sch ON job.job_id = sch.job_id
–where name like ‘%BACKUP%’ –you can also filter with where clause
order by name

 

 

 

Good Luck