Redigeeri

Jagamisviis:

sp_describe_cursor_columns (Transact-SQL)

  • Artikkel

Applies to: SQL Server

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

Transact-SQL syntax conventions

Syntax

sp_describe_cursor_columns
    [ @cursor_return = ] cursor_return OUTPUT
    , [ @cursor_source = ] { N'local' | N'global' | N'*cursor_source*' }
    , [ @cursor_identity = ] N'cursor_identity'
[ ; ]

Arguments

[ @cursor_return = ] cursor_return OUTPUT

The name of a declared cursor variable to receive the cursor output. @cursor_return is an OUTPUT parameter of type int, 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'cursor_source' }

Specifies whether the cursor being reported on is specified, by using the name of a local cursor, a global cursor, or a cursor variable. @cursor_source is nvarchar(30), with no default.

[ @cursor_identity = ] N'cursor_identity'

The name of a cursor created by a DECLARE CURSOR statement. @cursor_identity is nvarchar(128), with no default.

  • If cursor has the LOCAL keyword, or is defaulted to LOCAL, @cursor_identity is local.

  • If cursor has the GLOBAL keyword, or is defaulted to GLOBAL, @cursor_identity is global. @cursor_identity can also be the name of an API server cursor opened by an ODBC application, and then named by calling SQLSetCursorName.

  • Otherwise, @cursor_identity is the name of a cursor variable associated with an open cursor.

Return code values

None.

Cursors returned

sp_describe_cursor_columns encapsulates its report as a Transact-SQL cursor output parameter. This enables Transact-SQL batches, stored procedures, and triggers to work with the output one row at a time. This also means that the procedure can't be called directly from database API functions. The cursor output parameter must be bound to a program variable, but the database APIs don't support binding cursor parameters or variables.

The following table shows the format of the cursor returned by using sp_describe_cursor_columns.

Column name Data type Description
column_name sysname Name assigned to the result set column. The column is NULL if the column was specified without an accompanying AS clause.

Nullable.
ordinal_position int Relative position of the column from the leftmost column in the result set. The first column is in position 0.
column_characteristics_flags int A bitmask that indicates the information stored in DBCOLUMNFLAGS in OLE DB. Can be one or a combination of the following values:

1 = Bookmark
2 = Fixed length
4 = Nullable
8 = Row versioning
16 = Updatable column (set for projected columns of a cursor that's no FOR UPDATE clause and, if there's such a column, can be only one per cursor).

When bit values are combined, the characteristics of the combined bit values apply. For example, if the bit value is 6, the column is a fixed-length (2), nullable (4) column.
column_size int Maximum possible size for a value in this column.
data_type_sql smallint Number that indicates 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) 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 doesn't participate in ordering.

Nullable.
hidden_column smallint 0 = this column appears in the select list.

1 = 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 object or base table that is supplying the column. If the result set column was built from an expression, objectid is -1.
dbid int ID of the database that contains the base table that is supplying the column. If the result set column was built from an expression, dbid is -1.
dbname sysname Name of the database that contains the base table that is supplying the column. If the result set column was built from an expression, dbname is NULL.

Nullable.

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. To obtain a report of the Transact-SQL server cursors visible on the connection, use sp_cursor_list.

Permissions

Requires membership in the public role.

Examples

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

USE AdventureWorks2022;
GO
-- Declare and open a global cursor.
DECLARE abc CURSOR KEYSET FOR
    SELECT LastName
    FROM Person.Person;
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

Related content