sp-describe-cursor-tables-transact-sql.md

title	sp_describe_cursor_tables (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/16/2017
ms.prod	sql
ms.prod_service	database-engine
ms.reviewer
ms.technology	system-objects
ms.topic	language-reference
f1_keywords	sp_describe_cursor_tables_TSQL sp_describe_cursor_tables
dev_langs	TSQL
helpviewer_keywords	sp_describe_cursor_tables
ms.assetid	02c0f81a-54ed-4ca4-aa4f-bb7463a9ab9a
author	stevestein
ms.author	sstein

sp_describe_cursor_tables (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]

Reports the objects or base tables referenced by a server cursor.

Transact-SQL Syntax Conventions

Syntax

  
sp_describe_cursor_tables   
     [ @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_variableOUTPUT
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_tables 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 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. global_cursor_name 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_tables encapsulates its report as a [!INCLUDEtsql] cursor output parameter. This enables [!INCLUDEtsql] 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 API functions. The cursor output parameter must be bound to a program variable, but the APIs do not support bind cursor parameters or variables.

The following table shows the format of the cursor that is returned by sp_describe_cursor_tables.

Column name	Data type	Description
table owner	sysname	User ID of the table owner.
Table_name	sysname	Name of the object or base table. In [!INCLUDEssNoVersion], server cursors always return the user-specified object, not the base tables.
Optimizer_hints	smallint	Bitmap that is made up of one or more of the following: 1 = Row-level locking (ROWLOCK) 4 = Page-level locking (PAGELOCK) 8 = Table lock (TABLOCK) 16 = Exclusive table lock (TABLOCKX) 32 = Update lock (UPDLOCK) 64 = No lock (NOLOCK) 128 = Fast first-row option (FASTFIRST) 4096 = Read repeatable semantic when used with DECLARE CURSOR (HOLDLOCK) When multiple options are supplied, the system uses the most restrictive. However, sp_describe_cursor_tables shows the flags that are specified in the query.
lock_type	smallint	Scroll-lock type requested either explicitly or implicitly for each base table that underlies this cursor. The value can be one of the following: 0 = None 1 = Shared 3 = Update
server_name	sysname, nullable	Name of the linked server that the table resides on. NULL when OPENQUERY or OPENROWSET are used.
Objectid	int	Object ID of the table. 0 when OPENQUERY or OPENROWSET are used.
dbid	int	ID of the database that the table resides in. 0 when OPENQUERY or OPENROWSET are used.
dbname	sysname, nullable	Name of the database that the table resides in. NULL when OPENQUERY or OPENROWSET are used.

Remarks

sp_describe_cursor_tables describes the base tables that are referenced by a server cursor. For a description of the attributes of the result set returned by the cursor, use sp_describe_cursor_columns. For a description of the global characteristics of the cursor, such as its scrollability and updatability, use sp_describe_cursor. To obtain a report of the [!INCLUDEtsql] server cursors that are 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_tables to report on the tables that are referenced by the cursor.

USE AdventureWorks2012;  
GO  
-- Declare and open a global cursor.  
DECLARE abc CURSOR KEYSET FOR  
SELECT LastName  
FROM Person.Person  
WHERE LastName LIKE 'S%';  
  
OPEN abc;  
GO  
-- Declare a cursor variable to hold the cursor output variable  
-- from sp_describe_cursor_tables.  
DECLARE @Report CURSOR;  
  
-- Execute sp_describe_cursor_tables into the cursor variable.  
EXEC master.dbo.sp_describe_cursor_tables  
      @cursor_return = @Report OUTPUT,  
      @cursor_source = N'global', @cursor_identity = N'abc';  
  
-- Fetch all the rows from the sp_describe_cursor_tables 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_tables.  
CLOSE @Report;  
DEALLOCATE @Report;  
GO  
  
-- Close and deallocate the original cursor.  
CLOSE abc;  
DEALLOCATE abc;  
GO  

See Also

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