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, or ODBC 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.Note
In previous releases of SQL Server, columns were marked as updatable only if the column was explicitly included in the FOR UPDATE clause, but this did not actually guarantee that the column was updatable. In SQL Server 2005, columns are only marked as updatable only if they can actually be updated through the cursor.
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.
Be careful 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.
See Also
Other Resources
@@CURSOR_ROWS (Transact-SQL)
@@FETCH_STATUS (Transact-SQL)
CURSOR_STATUS (Transact-SQL)
sp_cursor_list (Transact-SQL)
sp_describe_cursor (Transact-SQL)
sp_describe_cursor_columns (Transact-SQL)
sp_describe_cursor_tables (Transact-SQL)