sp_describe_cursor_columns (Transact-SQL)

 

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

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
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' ]   
   }  

[ @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 by 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 that either has 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 that either has the GLOBAL keyword or that defaulted to GLOBAL. global_cursor_name is nvarchar(128).

global_cursor_name can also be the name of an API server cursor that is opened by an ODBC application and then named by calling SQLSetCursorName.

[ @cursor_identity= ] N'input_cursor_variable'
Is the name of a cursor variable associated with an open cursor. input_cursor_variable is nvarchar(128).

None

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 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.

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

Column nameData typeDescription
column_namesysname (nullable)Name assigned to the result set column. The column is NULL if the column was specified without an accompanying AS clause.
ordinal_positionintRelative position of the column from the leftmost column in the result set. The first column is in position 0.
column_characteristics_flagsintA bitmask that indicates the information stored in DBCOLUMNFLAGS in OLE DB. Can be one or a combination 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).

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_sizeintMaximum possible size for a value in this column.
data_type_sqlsmallintNumber that indicates the SQL Server data type of the column.
column_precisiontinyintMaximum precision of the column as per the bPrecision value in OLE DB.
column_scaletinyintNumber 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_positionintIf 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_directionvarchar(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_columnsmallint0 = this column appears in the select list.

1 = Reserved for future use.
columnidintColumn ID of the base column. If the result set column was built from an expression, columnid is -1.
objectidintObject 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.
dbidintID 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.
dbnamesysname

(nullable)
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.

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.

Requires membership in the public role.

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

USE AdventureWorks2012;  
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  

Cursors
CURSOR_STATUS (Transact-SQL)
DECLARE CURSOR (Transact-SQL)
sp_describe_cursor (Transact-SQL)
sp_cursor_list (Transact-SQL)
sp_describe_cursor_tables (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show: