sp_help_category (Transact-SQL)
Applies to:
SQL Server
Provides information about the specified classes of jobs, alerts, or operators.
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 thecategory_type
as a name.0
shows thecategory_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 = Local2 = Multiserver3 = 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
This stored procedure is owned by the db_owner role. You can grant EXECUTE
permissions for any user, but these permissions may be overridden during a SQL Server upgrade.
Other users must be granted one of the following SQL Server 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
EXEC 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
EXEC dbo.sp_help_category
@class = N'ALERT',
@name = N'Replication';
GO