Reports information about a specific cursor or all cursors that are active for your session.
sp_cursorinfo [{cursor_level | null}] [, cursor_name]
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.
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.
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)
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)
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.
Any user can execute sp_cursorinfo.
Commands declare cursor, set
