sp_cursorinfo

Description

Reports information about a specific cursor or all execute 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 c at level 0:

1> declare c cursor
2> for select au_id,au_lname, au_fname from authors
3> go
1> sp_cursorinfo
2> go
Cursor name 'c' is declared at nesting level '0'.
The cursor is declared as NON-SCROLLABLE cursor.
The cursor id is 917505.
The cursor has been successfully opened 0 times.
The cursor will remain open when a transaction is
committed or rolled back.
The number of rows returned for each FETCH is 1.
The cursor is updatable.
This cursor is using 5389 bytes of memory.

(return status = 0)

Example 2

Displays information on the cursor’s scrollability and sensitivity, in this case a semi-sensitive scrollable cursor css:

sp_cursorinfo 0, cursor_css
-------------

Cursor name 'css' is declared at nesting level '0'. 
The cursor is declared as SEMI_SENSITIVE SCROLLABLE cursor. 
The cursor id is 786434. 
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 on a row. 
There have been 1 rows read, 0 rows updated and 0 rows deleted through this
cursor.
The cursor will remain open when a transaction is committed or rolled back. 
The number of rows returned for each FETCH is 1. 
The cursor is read only. 
This cursor is using 19892 bytes of memory. 
There are 2 columns returned by this cursor. 
The result columns are: 
Name = 'c1', Table = 't1', Type = INT, Length = 4 (not updatable) 
Name = 'c2', Table = 't1', Type = INT, Length = 4 (not updatable)

Usage

Permissions

Any user can execute sp_cursorinfo.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands declare cursor, set