Getting Server Cursor Metadata

There are two ways to get metadata describing a server cursor:

• Applications using API server cursors with a database API such as ADO, OLE DB, ODBC, or DB-Library typically use the cursor functionality of the API to get information about the state of the cursor.
• Transact-SQL scripts, stored procedures and triggers can use the Transact-SQL functions and system stored procedures discussed in this topic to get information about a Transact-SQL cursor.

There are several system functions that report status information for a server cursor, or a server cursor assigned to a cursor variable:

• CURSOR_STATUS

  Indicates whether a cursor is open or closed, or if a cursor variable is currently associated with a cursor.

• @@FETCH_STATUS

  Indicates the success or failure of the last fetch operation performed for the connection.

• @@CURSOR_ROWS

  Reports the number of rows populated in the last cursor opened for the connection.

There are several system stored procedures that report the characteristics of a server cursor, or a server cursor assigned to a cursor variable:

• sp_describe_cursor

  Returns a cursor describing the attributes of a cursor, such as its scope, name, type, status, and the number of rows.

• sp_describe_cursor_columns

  Returns a cursor describing the attributes of each column in the cursor, such as the column’s name, position, size, and data type.

• sp_describe_cursor_tables

  Returns a cursor describing the base tables referenced by the cursor.

• sp_cursor_list

  Returns a cursor listing all the currently visible cursors for the connection. The format of the cursor returned by sp_cursor_list is the same as the cursor from sp_describe_cursor.

These system stored procedures return their result sets as output cursor variables. The database APIs do not support cursor variables, so these procedures cannot be called from applications, only within Transact-SQL scripts, stored procedures, and batches. Applications should use the cursor functionality of the database APIs to get the metadata for API server cursors.

Care must be taken with the status information returned by these functions and stored procedures, especially @@FETCH_STATUS. The information returned by @@FETCH_STATUS changes every time a FETCH statement is issued against any cursor open for the connection. A stored procedure or trigger that may need to refer to the status information after executing several additional statements should save @@FETCH_STATUS in an integer variable immediately after the FETCH statement. @@FETCH_STATUS may be reset even if there are no FETCH statements in the batch between the FETCH and the statement that tests the status. If an intervening INSERT, UPDATE or DELETE statement fires a trigger, the trigger can open and fetch from a cursor. @@FETCH_STATUS would then contain the status of the last FETCH statement in the trigger.

The stored procedures report their status information for a specific cursor, so their status information is not affected by operations on other cursors. Their status information is still affected by operations on the same cursor, so care must still be taken in using the status information returned by the stored procedures.