sp_help_jobstep

Returns information for the steps in a job.

Syntax

sp_help_jobstep { [ @job_id = ] 'job_id' | [ @job_name = ] 'job_name' }
     [ , [ @step_id = ] step_id ]
     [ , [ @step_name = ] 'step_name' ]
     [ , [ @suffix = ] suffix ]    /* not supported */

Arguments

[ @job_id = ] _’job_id’_

The job identification number. The job_id is uniqueidentifier, with a default of NULL.

[ @job_name = ] _’job_name’_

The name of the job. The name must be unique. job_name is sysname, with a default of NULL.

Note

Either job_id or job_name must be specified, but both cannot be specified.

[ @step_id = ] step_id

The identification number of the step in the job. If not included, all steps in the job are included. step_id is int, with a default of NULL.

[ @step_name = ] _’step_name’_

The name of the step in the job. _step_name_ is sysname, with a default of NULL.

[ @suffix = ] _suffix_

Not supported.

Result Sets

Column name

Data type

Description

step_id

int

Unique identifier for the step.

step_name

sysname

Name of the step in the job.

subsystem

nvarchar(40)

Not supported.

command

nvarchar(max)

Command executed in the step.

flags

int

A bitmask of values that control step behavior.

cmdexec_success_code

int

Not supported.

on_success_action

tinyint

Action to take if the step succeeds: 1 = Quit the job reporting success, 2 = Quit the job reporting failure, 3 = Go to the next step, 4 = Go to step.

on_success_step_id

int

If _on_success_action_ is 4, this indicates the next step to execute.

on_fail_action

tinyint

What to do if the step fails. Values are same as _on_success_action_.

on_fail_step_id

int

If _on_fail_action_ is 4, this indicates the next step to execute.

server

sysname

Not supported.

database_name

sysname

The database in which the command executes.

database_user_name

sysname

The database user context in which the command executes.

retry_attempts

int

Not supported.

retry_interval

int

Not supported.

os_run_priority

int

Not supported.

output_file_name

nvarchar(200)

Not supported.

last_run_outcome

int

Outcome of the step the last time it ran: 0 = Failed, 1 = Succeeded, 2 = Retry, 3 = Canceled, 5 = Unknown

last_run_duration

int

Duration (in seconds) of the step the last time it ran.

last_run_retries

int

Not supported.

last_run_date

int

Date the step last started execution.

last_run_time

int

Time the step last started execution.

proxy_id

int

Not supported.

Permissions

To run this stored procedure, users must be a member of the sysadmin fixed server role.

Examples

  1. Return information for all steps in a specific job.

The following example returns all the job steps for the job named “RebuildCache”.

EXEC dbo.sp_help_jobstep
    @job_name = N'RebuildCache';
  1. Return information about a specific job step.

EXEC dbo.sp_help_jobstep
    @job_name = N'RebuildCache',
    @step_id = 1 ;