sp_cursorinfo

Description

Reports information about a specific cursor or all cursors that are active for your session.

Syntax

sp_cursorinfo [{cursor_level | null}] [, cursor_name]

Parameters

cursor_level | null

is the level at which Adaptive Server returns information for the cursors. You can specify the following for cursor_level:

Level

Types of cursors

N

Any cursors declared inside stored procedures at a specific procedure nesting level. You can specify any positive number for its level.

0

Any cursors declared outside stored procedures.

-1

Any cursors from either of the above. You can substitute any negative number for this level.

If you want information about cursors with a specific cursor_name, regardless of cursor level, specify null for this parameter.

cursor_name

is the specific name for the cursor. Adaptive Server reports information about all active cursors that use this name at the cursor_level you specify. If you omit this parameter, Adaptive Server reports information about all the cursors at that level.

Examples

Example 1

Displays the information about the cursor named authors_crsr at level 0:

sp_cursorinfo 0, authors_crsr
Cursor name 'authors_crsr' is declared at nesting level '0'.
The cursor id is 327681
The cursor has been successfully opened 1 times.
The cursor was compiled at isolation level 0.
The cursor is not open.
The cursor will remain open when a transaction is commited or rolled back.
The number of rows returned for each FETCH is 1.
The cursor is read only.
There are 3 columns returned by this cursor.
The result columns are:
Name = 'au_id', Table = 'authors', Type = ID, 
     Length = 11 (read only)
Name = 'au_lname', Table = 'authors', Type = VARCHAR, 
Length = 40 (read only)
Name = 'au_fname', Table = 'authors', Type = VARCHAR, 
Length = 20 (read only)

Example 2

Displays the information about any cursors named author_sales declared by a user across all levels:

sp_cursorinfo null, author_sales
Cursor name 'author_sales' is declared on procedure 'au_sales'.
Cursor name 'author_sales' is declared at nesting level '1'.
The cursor id is 327682
The cursor has been successfully opened 1 times.
The cursor was compiled at isolation level 1.
The cursor is currently scanning at a nonzero isolation level.
The cursor is positioned after the last row.
The cursor will be closed when a transaction is commited or rolled back.
The number of rows returned for each FETCH is 1.
The cursor is updatable.
There are 3 columns returned by this cursor.
The result columns are:
Name = 'title_id', Table = 'titleauthor', Type = ID, 
      Length = 11 (updatable)
Name = 'title', Table = 'titles', Type = VARCHAR, 
      Length = 80 (updatable)
Name = 'total_sales', Table = 'titles', Type = INT (updatable)

Usage

Permissions

Any user can execute sp_cursorinfo.

See also

Commands declare cursor, set