In this article, I present three options for returning the steps of a SQL Server Agent job when using T-SQL.

The Options

You can use the following T-SQL options to return the steps of a SQL Server Agent job:

• Option 1: Execute the sp_help_job stored procedure.
• Option 2: Execute the sp_help_jobstep stored procedure.
• Option 3: Query the sysjobsteps table (and join it with sysjobs_view if required).

All of these options reside in the msdb database, and therefore need to be run in that database. You can do that by switching to the msdb database first, or by qualifying the object appropriately (e.g. msdb.dbo.sysjobsteps).

Option 1: sp_help_job

When you call sp_help_job without any arguments, it simply returns a list of jobs. But when you pass the name or ID of a job, it lists out the details for that job, including its steps.

Here’s an example:

EXEC sp_help_job 
	@job_name = 'SqlAgentTest';

Result:

This lists out the job, as well as any job steps, schedules, and target servers.

Here’s an example of the code when using the ID:

EXEC sp_help_job 
	@job_id = '343D9F2A-070A-4367-BF69-4248FFF57D70';

When using the ID, you can omit the parameter name if you wish.

Example:

EXEC sp_help_job '343D9F2A-070A-4367-BF69-4248FFF57D70';

Syntax

The full syntax of sp_help_job goes like this:

sp_help_job { [ @job_id = ] job_id  
[ @job_name = ] 'job_name' }   
     [ , [ @job_aspect = ] 'job_aspect' ]   
     [ , [ @job_type = ] 'job_type' ]   
     [ , [ @owner_login_name = ] 'login_name' ]   
     [ , [ @subsystem = ] 'subsystem' ]   
     [ , [ @category_name = ] 'category' ]   
     [ , [ @enabled = ] enabled ]   
     [ , [ @execution_status = ] status ]   
     [ , [ @date_comparator = ] 'date_comparison' ]   
     [ , [ @date_created = ] date_created ]   
     [ , [ @date_last_modified = ] date_modified ]   
     [ , [ @description = ] 'description_pattern' ]

See Microsoft’s documentation for sp_help_job for an explanation of each parameter, as well as the values that each accepts.

Option 2: sp_help_jobstep

The sp_help_jobstep stored procedure is designed specifically to return the steps of a job. But that’s all it returns.

So if you don’t want to see any of the schedule and target server stuff, etc, this could be what you’re looking for.

You can pass sp_help_jobstep the job name or its ID (but not both).

Here’s an example:

EXEC sp_help_jobstep
	@job_name = 'SqlAgentTest';

And here’s what the result looks like on a two-step job:

You can also specify a step ID if you only want a specific step to be returned. You still have to specify which job it’s for though (either with its ID or name).

Here’s an example of specifying the second step in the job:

EXEC sp_help_jobstep
	@job_name = 'SqlAgentTest', 
	@step_id = 2;

The step ID needs to actually exist for that job. For example, specifying a step ID of 3 for the above job results in an error.

Option 3: The sysjobsteps Table

Another option is to run a query against the sysjobsteps table.

This table contains the steps of all jobs, so if you only want the steps for a specific job, you’ll need to pass the job ID.

Here’s an example of querying the sysjobsteps table for the steps of a specific job:

SELECT * FROM msdb.dbo.sysjobsteps
WHERE job_id = '8A6E1BFF-9F46-4FF9-8E63-ABC8B224B6F8';

This returns a similar result to the sp_help_jobstep procedure.

If you want some of the job details to be returned (such as its name), you can run a join between sysjobsteps and sysjobs_view.

Example:

SELECT
	jv.name,
	jv.description,
	jv.start_step_id,
	js.step_id,
	js.step_name
FROM msdb.dbo.sysjobs_view jv
LEFT JOIN msdb.dbo.sysjobsteps js
ON jv.job_id = js.job_id;

Here’s the result in my test environment:

With this being a left join, it includes jobs that don’t have any steps (see the NULL in the step_id and step_name columns for the job called TestJob).

To limit it to just those jobs with steps, use an inner join.