Getting information about cursors

Use sp_cursorinfo to find information about a cursor’s name, its current status, and its result columns. This example displays information about authors_crsr:

sp_cursorinfo 0, authors_crsr

Cursor name 'authors_crsr' is declared at nesting level '0'.
The cursor is declared as NON-SCROLLABLE cursor.
The cursor id is 851969.
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 4 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 updatable.
This cursor is using 3432 bytes of memory.
There are 3 columns returned by this cursor.
The result columns are:
Name = 'au_id', Table = 'authors', Type = VARCHAR, Length = 11 (updatable)
Name = 'au_lname', Table = 'authors', Type = VARCHAR, Length = 40 (updatable)
Name = 'au_fname', Table = 'authors', Type = VARCHAR, Length = 20 (updatable)

Showplan output for the cursor:

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode

       STEP 1
           The type of query is DECLARE CURSOR.

           1 operator(s) under root

         |ROOT:EMIT Operator (VA = 1)
         |
         |  |SCAN Operator (VA = 0)
         |  |  FROM TABLE
         |  |  authors
         |  |  Using Clustered Index.
         |  |  Index : auidind
         |  |  Forward Scan.
         |  |  Positioning at start of table.
         |  |  Using I/O Size 2 Kbytes for data pages.
         |  |  With LRU Buffer Replacement Strategy for data pages.

This example displays information about scrollable cursors:

sp_cursorinfo 0, authors_scrollcrsr

Cursor name ’authors_scrollcrsr’ 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 = ’au_fname’, Table = ’authors’, Type =VARCHAR, Length = 20 (not updatable)
Name = ’au_lname’, Table = ’authors’, Type = VARCHAR, Length = 40 (not updatable)