In SQL Server, the sp_help_jobschedule stored procedure returns information about the scheduling of jobs.

This article provides an overview of this stored procedure including examples.

Syntax

The official syntax goes like this:

sp_help_jobschedule { [ @job_id = ] job_id | [ @job_name = ] 'job_name' }  
     [ , [ @schedule_name = ] 'schedule_name' ]  
     [ , [ @schedule_id = ] schedule_id ]  
     [ , [ @include_description = ] include_description ]

When you call sp_help_jobschedule, you must provide either the job name or its ID (but not both), or the schedule ID. You can also pass the schedule’s name but only in combination with the job ID or its name.

The accepted combination is a bit tricky. Basically, it goes like this:

If schedule_id is specified, neither job_id nor job_name can be specified. Otherwise, the job_id or job_name parameters can be used with schedule_name.

However, you won’t be able to use the schedule_id if a schedule is attached to multiple jobs.

Note that sp_help_jobschedule is in the msdb database, so it needs to be run from there. You can do this either by switching to the msdb database (e.g. with USE msdb), or by qualifying the procedure with the database name and schema (i.e. msdb.dbo.sp_help_jobschedule).

Example

Here’s an example to demonstrate how to return all schedules attached to a specific job:

EXEC sp_help_jobschedule
	@job_name = 'SqlAgentTest';

Result:

This returns a list of schedules that are attached to the job specified with the @job_name argument.

Alternatively, I could have passed the @job_id:

EXEC sp_help_jobschedule
	@job_id = '8A6E1BFF-9F46-4FF9-8E63-ABC8B224B6F8';

But in that case, I couldn’t use @job_name (it’s either one or the other).

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

EXEC sp_help_jobschedule '8A6E1BFF-9F46-4FF9-8E63-ABC8B224B6F8';

Using the @schedule_id Parameter

You can alternatively get a schedule based on its ID:

EXEC sp_help_jobschedule
	@schedule_id = 15;

This works fine as long as the schedule isn’t attached to multiple jobs.

Here’s what happens when a schedule is attached to multiple jobs:

EXEC sp_help_jobschedule
	@schedule_id = 14;

Result:

Msg 14369, Level 16, State 1, Procedure sp_help_jobschedule, Line 69 [Batch Start Line 22]
 The schedule ID "14" is used by more than one job. Specify the job_id.

And even though it asks to specify the job_id, if you do this, you’ll need to remove the schedule_id (as mentioned, if you pass a schedule_id, you can’t include the job_name or job_id).

To demonstrate this, if I simply add the job_id and keep the schedule_id there, I get the following error.

EXEC sp_help_jobschedule
	@job_id = '8A6E1BFF-9F46-4FF9-8E63-ABC8B224B6F8',
	@schedule_id = 14;

Result:

Msg 14273, Level 16, State 1, Procedure sp_help_jobschedule, Line 51 [Batch Start Line 22]
 You must provide either @job_id or @job_name (and, optionally, @schedule_name), or @schedule_id.

Include the Description

Another thing you can do is specify whether or not to include the schedule’s description.

By default, the description isn’t included in the result set. Actually, the schedule_description column is included, but it doesn’t contain the description, unless you explicitly request it. If you don’t request it, the schedule_description column displays (Description not requested.).

The @include_description parameter is bit, with a default of 0, which means it doesn’t display the description. Passing a value of 1 results in the description being displayed.

EXEC sp_help_jobschedule
	@job_name = 'SqlAgentTest',
	@include_description = 1;