Skip to content

sp-help-category-transact-sql.md

Latest commit

 

History

History
144 lines (101 loc) · 4.12 KB

sp-help-category-transact-sql.md

File metadata and controls

144 lines (101 loc) · 4.12 KB
title sp_help_category (Transact-SQL)
description Provides information about the specified classes of jobs, alerts, or operators.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sp_help_category
sp_help_category_TSQL
helpviewer_keywords
sp_help_category
dev_langs
TSQL

sp_help_category (Transact-SQL)

[!INCLUDE SQL Server]

Provides information about the specified classes of jobs, alerts, or operators.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_help_category
    [ [ @class = ] 'class' ]
    [ , [ @type = ] 'type' ]
    [ , [ @name = ] N'name' ]
    [ , [ @suffix = ] suffix ]
[ ; ]

Arguments

[ @class = ] 'class'

Specifies the class about which information is requested. @class is varchar(8), and can be one of these values.

Value Description
JOB (default) Provides information about a job category.
ALERT Provides information about an alert category.
OPERATOR Provides information about an operator category.

[ @type = ] 'type'

The type of category for which information is requested. @type is varchar(12), and can be one of these values.

Value Description
LOCAL Local job category.
MULTI-SERVER Multiserver job category.
NONE Category for a class other than JOB.

[ @name = ] N'name'

The name of the category for which information is requested. @name is sysname, with a default of NULL.

[ @suffix = ] suffix

Specifies whether the category_type column in the result set is an ID or a name. @suffix is bit, with a default of 0.

  • 1 shows the category_type as a name.
  • 0 shows the category_type as an ID.

Return code values

0 (success) or 1 (failure).

Result set

When @suffix is 0, sp_help_category returns the following result set:

Column name Data type Description
category_id int Category ID
category_type tinyint Type of category:

1 = Local
2 = Multiserver
3 = None
name sysname Category name

When @suffix is 1, sp_help_category returns the following result set:

Column name Data type Description
category_id int Category ID
category_type sysname Type of category. One of LOCAL, MULTI-SERVER, or NONE
name sysname Category name

Remarks

sp_help_category must be run from the msdb database.

If no parameters are specified, the result set provides information about all of the job categories.

Permissions

[!INCLUDE msdb-execute-permissions]

Other users must be granted one of the following [!INCLUDE ssNoVersion] 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.

Examples

A. Return local job information

The following example returns information about jobs that are administered locally.

USE msdb;
GO

EXECUTE dbo.sp_help_category @type = N'LOCAL';
GO

B. Return alert information

The following example returns information about the Replication alert category.

USE msdb;
GO

EXECUTE dbo.sp_help_category
    @class = N'ALERT',
    @name = N'Replication';
GO

Related content