Artikel
10/04/2012

sp_help_category (Transact-SQL)

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

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

[ @class=] 'class'
The class about which information is requested. class is varchar(8), with a default value of JOB. class can be one of these values.

Value

Description

JOB

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), with a default of NULL, 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= ] '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, and 0 shows it as an ID.

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

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

Return Code Values

0 (success) or 1 (failure)

Result Sets

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

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

By default, members of the sysadmin fixed server role can execute this stored procedure. 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. Returning 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. Returning 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

Dela via