sp_help_jobschedule (Transact-SQL)sp_help_jobschedule (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

返回有关 SQL Server Management StudioSQL Server Management Studio 用来执行自动活动的计划作业的信息。Returns information about the scheduling of jobs used by SQL Server Management StudioSQL Server Management Studio to perform automated activities.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

[ @job_id = ] job_id 作业标识号。[ @job_id = ] job_id The job identification number. job_id 的值为 uniqueidentifier,默认值为 NULL。job_id is uniqueidentifier, with a default of NULL.

[ @job_name = ] 'job_name' 作业的名称。[ @job_name = ] 'job_name' The name of the job. job_name 的默认值为 sysname,默认值为 NULL。job_name is sysname, with a default of NULL.

备注

必须指定 job_idjob_name ,但不能同时指定两者。Either job_id or job_name must be specified, but both cannot be specified.

[ @schedule_name = ] 'schedule_name' 作业的计划项的名称。[ @schedule_name = ] 'schedule_name' The name of the schedule item for the job. schedule_name 的默认值为 sysname,默认值为 NULL。schedule_name is sysname, with a default of NULL.

[ @schedule_id = ] schedule_id 作业的计划项的标识号。[ @schedule_id = ] schedule_id The identification number of the schedule item for the job. schedule_id 的值为 int,默认值为 NULL。schedule_id is int, with a default of NULL.

[ @include_description = ] include_description 指定是否在结果集中包含计划的说明。[ @include_description = ] include_description Specifies whether to include the description of the schedule in the result set. include_descriptionbit,默认值为 0include_description is bit, with a default of 0. include_description0 时,将不在结果集中包含计划的说明。When include_description is 0, the description of the schedule is not included in the result set. include_description1 时,将在结果集中包含计划的说明。When include_description is 1, the description of the schedule is included in the result set.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets

列名称Column name 数据类型Data type 说明Description
schedule_idschedule_id intint 计划标识号。Schedule identifier number.
schedule_nameschedule_name sysnamesysname 计划名称。Name of the schedule.
enabledenabled intint (1) 还是未启用计划 (0) 。Whether the schedule enabled (1) or not enabled (0).
freq_typefreq_type intint 指示何时执行作业的值。Value indicating when the job is to be executed.

1 = 一次1 = Once

4 = 每天4 = Daily

8 = 每周8 = Weekly

16 = 每月16 = Monthly

32 = 每月,相对于 freq_interval32 = Monthly, relative to the freq_interval

64 = 当 SQLServerAgent 服务启动时运行。64 = Run when SQLServerAgent service starts.
freq_intervalfreq_interval intint 执行作业的天数。Days when the job is executed. 此值取决于 freq_type 的值。The value depends on the value of freq_type. 有关详细信息,请参阅 (transact-sql)sp_add_schedule For more information, see sp_add_schedule (Transact-SQL).
freq_subday_typefreq_subday_type intint Freq_subday_interval 的单位。Units for freq_subday_interval. 有关详细信息,请参阅 (transact-sql)sp_add_schedule For more information, see sp_add_schedule (Transact-SQL).
freq_subday_intervalfreq_subday_interval intint 每次执行作业之间要发生的 freq_subday_type 周期数。Number of freq_subday_type periods to occur between each execution of the job. 有关详细信息,请参阅 (transact-sql)sp_add_schedule For more information, see sp_add_schedule (Transact-SQL).
freq_relative_intervalfreq_relative_interval intint 计划作业每月的 freq_intervalScheduled job's occurrence of the freq_interval in each month. 有关详细信息,请参阅 (transact-sql)sp_add_schedule For more information, see sp_add_schedule (Transact-SQL).
freq_recurrence_factorfreq_recurrence_factor intint 作业的已计划执行日期之间的间隔月数。Number of months between the scheduled execution of the job.
active_start_dateactive_start_date intint 激活计划的日期。Date the schedule is activated.
active_end_dateactive_end_date intint 计划的结束日期。End date of the schedule.
active_start_timeactive_start_time intint 计划开始的时间。Time of the day the schedule starts.
active_end_timeactive_end_time intint 计划结束的时间。Time of the day schedule ends.
date_createddate_created datetimedatetime 创建计划的日期。Date the schedule is created.
schedule_descriptionschedule_description nvarchar(4000)nvarchar(4000) 计划的英语说明,派生自 msdb.dbo.sys计划 中的值。An English description of the schedule that is derived from values in msdb.dbo.sysschedules. include_description0 时,此列包含文本,指出未请求说明。When include_description is 0, this column contains text stating that the description was not requested.
next_run_datenext_run_date intint 计划下一次引发作业运行的日期。Date the schedule will next cause the job to run.
next_run_timenext_run_time intint 计划下一次引发作业运行的时间。Time the schedule will next cause the job to run.
schedule_uidschedule_uid uniqueidentifieruniqueidentifier 计划的标识符。Identifier for the schedule.
job_countjob_count intint 返回的作业数。Count of jobs returned.

注意: sp_help_jobscheduledbo.sysjobschedules 返回值,并在 msdbdbo.sys计划 系统表。NOTE: sp_help_jobschedule returns values from the dbo.sysjobschedules and dbo.sysschedules system tables in msdb. sysjobschedules 每20分钟更新一次。sysjobschedules updates every 20 minutes. 这可能会影响此存储过程返回的值。This might affect the values that are returned by this stored procedure.

备注Remarks

Sp_help_jobschedule 的参数只能在某些组合中使用。The parameters of sp_help_jobschedule can be used only in certain combinations. 如果指定 schedule_id ,则 job_idjob_name 都无法指定。If schedule_id is specified, neither job_id nor job_name can be specified. 否则, job_idjob_name 参数可与 schedule_name 一起使用。Otherwise, the job_id or job_name parameters can be used with schedule_name.

权限Permissions

要求具有 sysadmin 固定服务器角色的成员身份。Requires membership in the sysadmin fixed server role. 其他用户必须被授予 SQL ServerSQL Server msdb 数据库中下列 代理固定数据库角色的权限之一:Other users must be granted one of the following SQL ServerSQL Server Agent fixed database roles in the msdb database:

  • SQLAgentUserRoleSQLAgentUserRole

  • SQLAgentReaderRoleSQLAgentReaderRole

  • SQLAgentOperatorRoleSQLAgentOperatorRole

有关这些角色的权限的详细信息,请参阅 SQL Server 代理固定数据库角色For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

SQLAgentUserRole 的成员只能查看其所拥有的作业计划的属性。Members of SQLAgentUserRole can only view properties of job schedules that they own.

示例Examples

A.A. 返回特定作业的作业计划Returning the job schedule for a specific job

以下示例返回名为 BackupDatabase 的作业的计划信息。The following example returns the scheduling information for a job named BackupDatabase.

USE msdb ;  
GO  
  
EXEC dbo.sp_help_jobschedule  
    @job_name = N'BackupDatabase' ;  
GO  

B.B. 返回特定计划的作业计划Returning the job schedule for a specific schedule

下面的示例返回名为 NightlyJobs 的计划和名为 RunReports 的作业的信息。The following example returns the information for the schedule named NightlyJobs and the job named RunReports.

USE msdb ;  
GO  
  
EXEC dbo.sp_help_jobschedule   
    @job_name = N'RunReports',  
    @schedule_name = N'NightlyJobs' ;  
GO  

C.C. 返回特定计划的作业计划和计划说明Returning the job schedule and schedule description for a specific schedule

下面的示例返回名为 NightlyJobs 的计划和名为 RunReports 的作业的信息。The following example returns the information for the schedule named NightlyJobs and the job named RunReports. 返回的结果集包括对计划的说明。The result set returned includes a description of the schedule.

USE msdb ;  
GO  
  
EXEC dbo.sp_help_jobschedule  
    @job_name = N'RunReports',  
    @schedule_name = N'NightlyJobs',  
    @include_description = 1 ;  
GO  

另请参阅See Also

sp_add_schedule (Transact-sql) sp_add_schedule (Transact-SQL)
sp_delete_schedule (Transact-sql) sp_delete_schedule (Transact-SQL)
sp_update_schedule (Transact-sql) sp_update_schedule (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)