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

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

• If you do not specify either cursor_level or cursor_name, Adaptive Server displays information about all active cursors. Active cursors are those declared by you and allocated by Adaptive Server.

• Adaptive Server reports the following information about each cursor:

  • The cursor name, its nesting level, its cursor ID, and the procedure name (if it is declared in a stored procedure).

  • The number of times the cursor has been opened.

  • The isolation level (0, 1, or 3) in which it was compiled and in which it is currently scanning (if open).

  • Whether the cursor is open or closed. If the cursor is open, it indicates the current cursor position and the number of rows fetched.

  • Whether the open cursor will be closed if the cursor’s current position is deleted.

  • Whether the cursor will remain open or be closed if the cursor’s current transaction is committed or rolled back.

  • The number of rows returned for each fetch of that cursor.

  • Whether the cursor is updatable or read-only.

  • The number of columns returned by the cursor. For each column, it displays the column name, the table name or expression result, and whether it is updatable.

  The output from sp_cursorinfo varies, depending on the status of the cursor. In addition to the information listed, sp_cursorinfo displays the showplan output for the cursor. For more information about showplan, see the Performance and Tuning Guide.

Permissions

Any user can execute sp_cursorinfo.

Auditing

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

See also