In SQL Server, the sp_help_schedule stored procedure returns information about schedules in the SQL Server Agent.

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

Syntax

The official syntax goes like this:

sp_help_schedule   
     [ @schedule_id = ] id ,  
     [ @schedule_name = ] 'schedule_name'   
     [ , [ @attached_schedules_only = ] attached_schedules_only ]  
     [ , [ @include_description = ] include_description ]

You can call sp_help_schedule, with or without parameters.

When you call it without any parameters, it returns a list of all schedules in the instance.

When you pass either a schedule name or ID, it returns just that schedule.

Note that sp_help_schedule 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_schedule).

Example

Here’s an example to demonstrate how to return all schedules in the instance:

EXEC msdb.dbo.sp_help_schedule;

Result:

Return a Schedule Based on its Name

You can also get a schedule based on its name:

EXEC msdb.dbo.sp_help_schedule
	@schedule_name = 'Weekly_Sun_3AM';

Return a Schedule Based on its ID

You can alternatively get a schedule based on its ID:

EXEC msdb.dbo.sp_help_schedule
	@schedule_id = 14;

Return only Attached Schedules

Although sp_help_schedule doesn’t return schedules for a specific job (use sp_help_jobschedule for that), you can use it to return only those schedules that are attached to a job.

You can do this with the @attached_schedules_only parameter.

The @attached_schedules_only parameter is bit, with a default of 0, which means it returns both attached and unattached schedules. Passing a value of 1 limits it to just attached schedules.

Example:

EXEC sp_help_schedule
	@attached_schedules_only = 1;

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;