sp_describe_cursor
Reports the attributes of a server cursor.
Syntax
sp_describe_cursor [ @cursor_return = ] output_cursor_variable OUTPUT
{ [ , [ @cursor_source = ] N'local'
, [ @cursor_identity = ] N'local_cursor_name' ]
| [ , [ @cursor_source = ] N'global'
, [ @cursor_identity = ] N'global_cursor_name' ]
| [ , [ @cursor_source = ] N'variable'
, [ @cursor_identity = ] N'input_cursor_variable' ]
}
Arguments
[@cursor_return =] output_cursor_variable OUTPUT
Is the name of a declared cursor variable to receive the cursor output. output_cursor_variable is cursor, with no default, and must not be associated with any cursors at the time sp_describe_cursor is called. The cursor returned is a scrollable, dynamic, read-only cursor.
[@cursor_source =] { N'local' | N'global' | N'variable' }
Specifies whether the cursor being reported on is specified using the name of a local cursor, a global cursor, or a cursor variable. The parameter is nvarchar(30).
[@cursor_identity =] N'local_cursor_name']
Is the name of a cursor created by a DECLARE CURSOR statement either having the LOCAL keyword, or that defaulted to LOCAL. local_cursor_name is nvarchar(128).
[@cursor_identity =] N'global_cursor_name']
Is the name of a cursor created by a DECLARE CURSOR statement either having the GLOBAL keyword, or that defaulted to GLOBAL. It can also be the name of an API server cursor opened by an ODBC application that then named the cursor by calling SQLSetCursorName. global_cursor_name is nvarchar(128).
[@cursor_identity =] N'input_cursor_variable']
Is the name of a cursor variable associated with an open cursor. input_cursor_variable is nvarchar(128).
Return Code Values
None
Cursors Returned
sp_describe_cursor encapsulates its result set in a Transact-SQL cursor output parameter. This allows Transact-SQL batches, stored procedures, and triggers to work with the output one row at a time. It also means that the procedure cannot be called directly from database API functions. The cursor output parameter must be bound to a program variable, but the database APIs do not support binding cursor parameters or variables.
This is the format of the cursor returned by sp_describe_cursor. The format of the cursor is the same as the format returned by sp_cursor_list.
| Column name | Data type | Description |
|---|---|---|
| reference_name | sysname | Name used to refer to the cursor. If the reference to the cursor was through the name given on a DECLARE CURSOR statement, the reference name is the same as cursor name. If the reference to the cursor was through a variable, the reference name is the name of the variable. |
| cursor_name | sysname | Name of the cursor from a DECLARE CURSOR statement. If the cursor was created by setting a cursor variable to a cursor, the cursor name is a system-generated name. |
| cursor_scope | tinyint | 1 = LOCAL 2 = GLOBAL |
| status | int | Same values as reported by the CURSOR_STATUS system function:
1 = The cursor referenced by the cursor name or variable is open. If the cursor is insensitive, static, or keyset, it has at least one row. If the cursor is dynamic, the result set has zero or more rows. |
| model | tinyint | 1 = Insensitive (or static) 2 = Keyset 3 = Dynamic 4 = Fast Forward |
| concurrency | tinyint | 1 = Read-only 2 = Scroll locks 3 = Optimistic |
| scrollable | tinyint | 0 = Forward-only 1 = Scrollable |
| open_status | tinyint | 0 = Closed 1 = Open |
| cursor_rows | decimal(10,0) | Number of qualifying rows in the result set. For more information, see @@CURSOR_ROWS. |
| fetch_status | smallint | Status of the last fetch on this cursor. For more information, see @@FETCH_STATUS.
0 = Fetch successful. |
| column_count | smallint | Number of columns in the cursor result set. |
| row_count | decimal(10,0) | Number of rows affected by the last operation on the cursor. For more information, see @@ROWCOUNT. |
| last_operation | tinyint | Last operation performed on the cursor:
0 = No operations have been performed on the cursor. |
| cursor_handle | int | A unique value for the cursor within the scope of the server. |
Remarks
sp_describe_cursor describes the attributes that are global to a server cursor, such as the ability to scroll and update. Use sp_describe_cursor_columns for a description of the attributes of the result set returned by the cursor. Use sp_describe_cursor_tables for a report of the base tables referenced by the cursor. Use sp_cursor_list to get a report of the Transact-SQL server cursors visible on the connection.
A DECLARE CURSOR statement may request a cursor type that Microsoft® SQL Server™ cannot support with the SELECT statement contained in the DECLARE CURSOR. SQL Server implicitly converts the cursor to a type it can support with the SELECT statement. If TYPE_WARNING is specified in the DECLARE CURSOR statement SQL Server sends the application an informational message that a conversion has been done. sp_describe_cursor can then be called to determine the type of cursor that has been implemented.
Permissions
Execute permission defaults to the public role.
Examples
This example opens a global cursor and uses sp_describe_cursor to report on the attributes of the cursor.
USE Northwind
GO
-- Declare and open a global cursor.
DECLARE abc CURSOR STATIC FOR
SELECT LastName
FROM Employees
OPEN abc
-- Declare a cursor variable to hold the cursor output variable
-- from sp_describe_cursor.
DECLARE @Report CURSOR
-- Execute sp_describe_cursor into the cursor variable.
EXEC master.dbo.sp_describe_cursor @cursor_return = @Report OUTPUT,
@cursor_source = N'global', @cursor_identity = N'abc'
-- Fetch all the rows from the sp_describe_cursor output cursor.
FETCH NEXT from @Report
WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT from @Report
END
-- Close and deallocate the cursor from sp_describe_cursor.
CLOSE @Report
DEALLOCATE @Report
GO
-- Close and deallocate the original cursor.
CLOSE abc
DEALLOCATE abc
GO