sp_help_schedule (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Lists information about schedules.

Topic link icon Transact-SQL Syntax Conventions

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

[ @schedule_id = ] id
The identifier of the schedule to list. schedule_name is int, with no default. Either schedule_id or schedule_name may be specified.

[ @schedule_name = ] 'schedule_name'
The name of the schedule to list. schedule_name is sysname, with no default. Either schedule_id or schedule_name may be specified.

[ @attached_schedules_only = ] attached_schedules_only ]
Specifies whether to show only schedules that a job is attached to. attached_schedules_only is bit, with a default of 0. When attached_schedules_only is 0, all schedules are shown. When attached_schedules_only is 1, the result set contains only schedules that are attached to a job.

[ @include_description = ] include_description
Specifies whether to include descriptions in the result set. include_description is bit, with a default of 0. When include_description is 0, the schedule_description column of the result set contains a placeholder. When include_description is 1, the description of the schedule is included in the result set.

0 (success) or 1 (failure)

This procedure returns the following result set:

Column nameData typeDescription
schedule_idintSchedule identifier number.
schedule_uiduniqueidentifierIdentifier for the schedule.
schedule_namesysnameName of the schedule.
enabledintWhether the schedule enabled (1) or not enabled (0).
freq_typeintValue indicating when the job is to be executed.

 1 = Once

 4 = Daily

 8 = Weekly

 16 = Monthly

 32 = Monthly, relative to the freq_interval

 64 = Run when SQLServerAgent service starts.
freq_intervalintDays when the job is executed. The value depends on the value of freq_type. For more information, see sp_add_schedule (Transact-SQL).
freq_subday_typeintUnits for freq_subday_interval. For more information, see sp_add_schedule (Transact-SQL).
freq_subday_intervalintNumber of freq_subday_type periods to occur between each execution of the job. For more information, see sp_add_schedule (Transact-SQL).
freq_relative_intervalintScheduled job's occurrence of the freq_interval in each month. For more information, see sp_add_schedule (Transact-SQL).
freq_recurrence_factorintNumber of months between the scheduled execution of the job.
active_start_dateintDate the schedule is activated.
active_end_dateintEnd date of the schedule.
active_start_timeintTime of the day the schedule starts.
active_end_timeintTime of the day schedule ends.
date_createddatetimeDate the schedule is created.
schedule_descriptionnvarchar(4000)An English description of the schedule (if requested).
job_countintReturns how many jobs reference this schedule.

When no parameters are provided, sp_help_schedule lists information for all schedules in the instance.

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

  • SQLAgentUserRole

  • SQLAgentReaderRole

  • SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Members of SQLAgentUserRole can only view the schedules that they own.

A. Listing information for all schedules in the instance

The following example lists information for all schedules in the instance.

USE msdb ;  
GO  
  
EXEC dbo.sp_help_schedule ;  
GO  

B. Listing information for a specific schedule

The following example lists information for the schedule named NightlyJobs.

USE msdb ;  
GO  
  
EXEC dbo.sp_help_schedule  
    @schedule_name = N'NightlyJobs' ;  
GO  

sp_add_schedule (Transact-SQL)
sp_attach_schedule (Transact-SQL)
sp_delete_schedule (Transact-SQL)
sp_detach_schedule (Transact-SQL)

Community Additions

ADD
Show: