sp_describe_cursor_tables (T-SQL)

Reports the base tables referenced by a server cursor.

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_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_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 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_tables 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 bind cursor parameters or variables.

This is the format of the cursor 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 base table.
Optimizer_hints smallint Bitmap consisting of one or more of:
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 as 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:
0 = None
1 = Shared
3 = Update
server_name sysname, nullable Name of the linked server the table resides on. NULL if OPENQUERY or OPENROWSET are used.
Objectid int Object ID of the table. 0 if OPENQUERY or OPENROWSET are used.
dbid int ID of the database the table resides in. 0 if OPENQUERY or OPENROWSET are used.
dbname sysname, nullable Name of the database the table resides in. NULL if OPENQUERY or OPENROWSET are used.

Remarks

sp_describe_cursor_tables describes the base tables referenced by a server cursor. Use sp_describe_cursor_columns for a description of the attributes of the result set returned by the cursor. Use sp_describe_cursor for a description of the global characteristics of the cursor, such as its scrollability and updatability. 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_tables to report on the tables referenced by the cursor.

USE Northwind

GO

-- Declare and open a global cursor.

DECLARE abc CURSOR KEYSET FOR

SELECT LastName

FROM Employees

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 sp_describe_cursor
CURSOR_STATUS sp_describe_cursor_columns
DECLARE CURSOR System Stored Procedures
sp_cursor_list  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.