Reports the base tables referenced by a server cursor.
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']
}
None
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. |
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.
Execute permissions default to the public role.
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
Cursors | sp_describe_cursor |
CURSOR_STATUS | sp_describe_cursor_columns |
DECLARE CURSOR | System Stored Procedures |
sp_cursor_list |