sp_help_jobschedule (T-SQL)

Returns information about the scheduling of jobs used by SQL Server Enterprise Manager to perform automated activities.

Syntax

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

Arguments

[@job_id =] job_id: Is the job identification number. job_id is uniqueidentifier, with a default of NULL.
[@job_name =] 'job_name': Is the name of the job. job_name is varchar(100), with a default of NULL.

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

[@schedule_name =] 'schedule_name': Is the name of the schedule item for the job. schedule_name is varchar(100), with a default of NULL.
{[@schedule_id =] schedule_id: Is the identification number of the schedule item for the job. schedule_id is int, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name	Data type	Description
schedule_id	int	Schedule identifier number.
schedule_name	sysname	Name of the schedule.
enabled	int	Whether the schedule enabled (1) or not enabled (0).
freq_type	int	Value 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 SQL Server Agent starts.
freq_interval	int	Days when the job is executed. The value depends on the value of freq_type. For more information, see sp_add_jobschedule.
freq_subday_type	int	Units for freq_subday_interval. For more information, see sp_add_jobschedule.
freq_subday_interval	int	Number of freq_subday_type periods to occur between each execution of the job. For more information, see sp_add_jobschedule.
freq_relative_interval	int	Scheduled job’s occurrence of the freq_interval in each month. For more information, see sp_add_jobschedule.
freq_recurrence_factor	int	Number of months between the scheduled execution of the job.
active_start_date	int	Date the schedule is activated.
active_end_date	int	End date of the schedule.
active_start_time	int	Time of the day the schedule starts.
active_end_time	int	Time of the day schedule ends.
date_created	datetime	Date the schedule is created.
schedule_description	nvarchar(4000)	An English description of the schedule (if scheduled).
next_run_date	int	Date the schedule will next cause the job to run.
next_run_time	int	Time the schedule will next cause the job to run.

Remarks

The parameters of sp_help_jobschedule can be used only in certain combinations. 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.

Permissions

Execute permissions default to the public role. Anyone who can execute this procedure can also create, delete, or update a job, job step, job category, job schedule, job server, task, or job history information.

Examples

A. Return the job schedule for a specific job

This example returns the scheduling information for a job named Archive Tables.

USE msdb

EXEC sp_help_jobhistory @job_name = 'Archive Tables'

B. Return the job schedule for a named item in the schedule

This example returns the history for a job named Archive Tables and for its schedule item Weekly Archive.

USE msdb

EXEC sp_help_jobhistory @job_name = 'Archive Tables',

@schedule_name = 'Weekly Archive'

sp_add_jobschedule	sp_update_jobschedule
sp_delete_jobschedule	System Stored Procedures