Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Reports the objects or base tables referenced by a server cursor.
.gif "Topic link icon")
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
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 Transact-SQL server cursors that are visible on the connection, use sp_cursor_list.
Permissions
Requires membership in the public role.
Cursors Returned
sp_describe_cursor_tables 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 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 SQL Server 2005, server cursors always return the user-specified object, not the base tables. In SQL Server 2000, server cursors return the base tables, unless the view or function is created by using WITH VIEW_METADATA. |
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. |
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 AdventureWorks
GO
-- Declare and open a global cursor.
DECLARE abc CURSOR KEYSET FOR
SELECT LastName
FROM Person.Contact
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
Reference
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)