sp_describe_cursor_columns (T-SQL)

Reports the attributes of the columns in the result set of a server cursor.

Syntax

sp_describe_cursor_columns
[@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_columns 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_columns encapsulates its report as 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_columns.

Column name	Data type	Description
column_name	sysname nullable	Name assigned to the result set column. The column is NULL if the column was specified without an accompanying AS clause.
ordinal_position	int	Relative position of the column from the leftmost column in the result set. The first column is in position 1. The value for any hidden columns is 0.
column_characteristics_flags	int	A bitmask indicating the information stored in DBCOLUMNFLAGS in OLE DB. Can be one of the following: 1 = Bookmark 2 = Fixed length 4 = Nullable 8 = Row versioning 16 = Updatable column (set for projected columns of a cursor that has no FOR UPDATE clause and, if there is such a column, can be only one per cursor).
column_size	int	Maximum possible size for a value in this column.
data_type_sql	smallint	Number indicating the SQL Server data type of the column.
column_precision	tinyint	Maximum precision of the column as per the bPrecision value in OLE DB.
column_scale	tinyint	Number of digits to the right of the decimal point for the numeric or decimal data types as per the bScale value in OLE DB.
order_position	int	If the column participates in the ordering of the result set, the position of the column in the order key relative to the leftmost column.
order_direction	varchar(1), nullable	A = The column is in the order key and the ordering is ascending. D = The column is in the order key and the ordering is descending. NULL = The column does not participate in ordering.
hidden_column	smallint	If a value of 0, this column appears in the select list. The value 1 is reserved for future use.
columnid	int	Column ID of the base column. If the result set column was built from an expression, columnid is -1.
objectid	int	Object ID of the base table supplying the column. If the result set column was built from an expression, objectid is -1.
dbid	int	ID of the database containing the base table supplying the column. If the result set column was built from an expression, dbid is -1.
dbname	sysname nullable	Name of the database containing the base table supplying the column. If the result set column was built from an expression, dbname is NULL.

Remarks

sp_describe_cursor_columns describes the attributes of the columns in the result set of a server cursor, such as the name and data type of each cursor. Use sp_describe_cursor for a description of the global attributes of the server 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.

Permissions

Execute permissions default to the public role.

Examples

This example opens a global cursor and uses sp_describe_cursor_columns to report on the columns used in the cursor.

USE Northwind

GO

-- Declare and open a global cursor.

DECLARE abc CURSOR KEYSET FOR

SELECT LastName

FROM Employees

GO

OPEN abc

-- Declare a cursor variable to hold the cursor output variable

-- from sp_describe_cursor_columns.

DECLARE @Report CURSOR

-- Execute sp_describe_cursor_columns into the cursor variable.

EXEC master.dbo.sp_describe_cursor_columns

@cursor_return = @Report OUTPUT,

@cursor_source = N'global', @cursor_identity = N'abc'

-- Fetch all the rows from the sp_describe_cursor_columns 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_columns.

CLOSE @Report

DEALLOCATE @Report

GO

-- Close and deallocate the original cursor.

CLOSE abc

DEALLOCATE abc

GO

Cursors	sp_describe_cursor
CURSOR_STATUS	sp_describe_cursor_tables
DECLARE CURSOR	System Stored Procedures
sp_cursor_list