In this article, I present four options for using T-SQL to return the schedules that are attached to a SQL Server Agent job.

The Options

You can use the following T-SQL options to return the schedules of a SQL Server Agent job:

• Option 1: Execute the sp_help_job stored procedure.
• Option 2: Execute the sp_help_schedule stored procedure.
• Option 3: Execute the sp_help_jobschedule stored procedure.
• Option 4: Query the sysschedules table (and join it with sysjobschedules and sysjobs_view).

All of these options reside in the msdb database, and therefore need to be run in that database. You can do that by switching to the msdb database first, or by qualifying the object appropriately (e.g. msdb.dbo.sysschedules).

Option 1: The sp_help_job Stored Procedure

When you call sp_help_job without any arguments, it simply returns a list of jobs. But when you pass the name or ID of a job, it lists out the details for that job, including its schedules.

Here’s an example:

USE msdb;
EXEC sp_help_job 
	@job_name = 'SqlAgentTest';

Result:

This lists out the job, as well as any job steps, schedules, and target servers. In this case, there are two schedules attached to the job.

Here’s an example of the code when using the ID:

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

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

Example:

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

Syntax

The full syntax of sp_help_job goes like this:

sp_help_job { [ @job_id = ] job_id  
[ @job_name = ] 'job_name' }   
     [ , [ @job_aspect = ] 'job_aspect' ]   
     [ , [ @job_type = ] 'job_type' ]   
     [ , [ @owner_login_name = ] 'login_name' ]   
     [ , [ @subsystem = ] 'subsystem' ]   
     [ , [ @category_name = ] 'category' ]   
     [ , [ @enabled = ] enabled ]   
     [ , [ @execution_status = ] status ]   
     [ , [ @date_comparator = ] 'date_comparison' ]   
     [ , [ @date_created = ] date_created ]   
     [ , [ @date_last_modified = ] date_modified ]   
     [ , [ @description = ] 'description_pattern' ]

See Microsoft’s documentation for sp_help_job for an explanation of each parameter, as well as the values that each accepts.

Option 2: The sp_help_schedule Stored Procedure

The sp_help_schedule stored procedure is designed specifically to return schedules.

If you execute it without passing any arguments, it will return all schedules in the instance:

EXEC sp_help_schedule;

To get information for a specific schedule, pass either the schedule’s name or its ID:

EXEC sp_help_schedule
	@schedule_name = 'Run_Sat_6AM';

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

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;

Another thing you can do is use the @include_description parameter to specify whether or not to include the schedule’s description in the result set.

See Using sp_help_schedule in SQL Server for more information and examples.

Option 3: The sp_help_jobschedule Stored Procedure

The sp_help_jobschedule stored procedure returns information about the scheduling of jobs.

Example:

EXEC sp_help_jobschedule
	@job_name = 'SqlAgentTest';

This returns a list of schedules that are attached to the specified job.

You can also pass a schedule’s ID or name to return just that schedule.

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_name but only in combination with the job ID or its name.

The accepted combination of arguments is a bit tricky. See Using sp_help_jobschedule in SQL Server for a more detailed explanation and more examples.

Option 4: The sysschedules table (and others)

You can query the sysschedules table and join it with tables such as sysjobschedules and sysjobs_view to return a list of jobs and their associated schedules.

Example:

SELECT
	jv.name,
	jv.description,
	s.schedule_id,
	s.name,
	s.freq_type,
	s.freq_interval,
	s.freq_subday_type,
	s.freq_subday_interval,
	js.next_run_date,
	js.next_run_time
FROM msdb.dbo.sysjobs_view jv
INNER JOIN msdb.dbo.sysjobschedules js
ON jv.job_id = js.job_id
INNER JOIN msdb.dbo.sysschedules s
ON s.schedule_id = js.schedule_id
ORDER BY jv.name, s.name;

Result:

In this case I used inner joins to return only schedules that have an associated job and vice-versa.

You could switch to right joins to return all schedules, regardless of whether they’re attached to a job, or left joins to return all jobs, regardless of whether they have an attached schedule. Or you could use full joins to simply return everything.