Often the quickest way to get a list of SQL Server Agent jobs is to simply expand the SQL Server Agent node in the SSMS Object Explorer.

But that’s not the only way. And depending on your environment, it might not even be an option. If you don’t have SSMS or a database tool that allows you to see the SQL Server Agent jobs, you may need to use T-SQL instead.

In this article, I present three options for returning a list of SQL Server Agent jobs when using T-SQL.

The Options

You can use the following options to return a list of SQL Server Agent jobs with T-SQL:

• Option 1: Execute the sp_help_job stored procedure.
• Option 2: Query the sysjobs_view view.
• Option 3: Query the sysjobs table directly.

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.sysjobs_view).

Option 1: sp_help_job

When using sp_help_job to return a list of SQL Server Agent jobs, simply execute it without any arguments.

Like this:

USE msdb;
EXEC sp_help_job;

In this case I switched to the msdb database first, which meant that I didn’t need to fully qualify the procedure with the database name and schema.

Here’s what that looks like when I use SSMS to execute it in my test environment:

Return a Specific Job

You can use the same stored procedure to return the details of any one of those jobs. When you do that, you get a lot more detail than you get when listing out all the jobs.

To get the job details, you need to provide the job name or ID.

Here’s an example:

EXEC sp_help_job 
	@job_name = 'SqlAgentTest';

Result:

This lists out the job, as well as any job steps, schedules, and target servers.

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

EXEC sp_help_job 
	@job_id = '343D9F2A-070A-4367-BF69-4248FFF57D70';

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

Example:

EXEC sp_help_job '343D9F2A-070A-4367-BF69-4248FFF57D70';

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 sysjobs_view View

You can use the sysjobs_view to return a list of SQL Server Agent jobs.

Example:

SELECT * FROM msdb.dbo.sysjobs_view;

In this case I qualified the view name with the database and schema.

This view returns a similar (but slightly different) result set to the sp_help_job stored procedure when used without any arguments.

If you want job details such as steps, schedules, etc, you’ll need to join it with other tables, such as sysjobsteps, sysjobschedules, sysjobactivity, sysjobhistory, etc.

Here’s an example of joining it with the sysjobsteps table:

SELECT
	jv.name,
	jv.description,
	jv.start_step_id,
	js.step_id,
	js.step_name
FROM msdb.dbo.sysjobs_view jv
LEFT JOIN msdb.dbo.sysjobsteps js
ON jv.job_id = js.job_id;

Here’s the result in my test environment:

With this being a left join, it includes jobs that don’t have any steps (see the NULL in the step_id and step_name columns for the job called TestJob).

To limit it to just those jobs with steps, use an inner join.

Option 3: The sysjobs Table

Here’s an example of going directly to the sysjobs table:

SELECT * FROM msdb.dbo.sysjobs;

As with the view, if you want job details such as steps, schedules, etc, you’ll need to join it with other tables, such as sysjobsteps, sysjobschedules, sysjobactivity, sysjobhistory, etc.

It’s usually recommended to query the view rather than the table directly.