If you already have a reference (e.g. via a variable or a parameter) to cursors in the current scope, then it suffices to call sp_describe_cursor, and probably sp_describe_cursor_columns and sp_describe_cursor_tables. If you don't have a reference (e.g. within a stored procedure that is called from the body of a cursor) then you can just call sp_cursor_list. Well ... 'just call' may be an understatement. All these system stored procedures use a cursor as OUTPUT variable, so there's some work involved. In a sense I find it cruel that in order to see details about cursors you are forced to create yet another cursor. But anyway, here's how you can find out if there are cursors hanging around in your current scope:
USE AdventureWorksGO
/************************//* Using sp_cursor_list *//************************/-- More info about the returned columns:-- http://msdn.microsoft.com/en-us/library/ms186256.aspx-- Declare and open a keyset-driven cursor.DECLARE c1 CURSOR GLOBAL DYNAMIC
FOR SELECT LastName FROM Person.ContactWHERE LastName LIKE 'S%'
ORDER BY LastNameOPEN c1-- Declare a cursor variable to hold the cursor output variable-- from sp_cursor_list.DECLARE @Report CURSOR
-- Execute sp_cursor_list into the cursor variable.EXEC master.dbo.sp_cursor_list @cursor_return = @Report OUTPUT, @cursor_scope = 3 -- Local and Global-- Fetch all the rows from the sp_cursor_list output cursor.FETCH NEXT from @ReportWHILE (@@FETCH_STATUS <> -1)BEGIN FETCH NEXT from @ReportEND-- Close and deallocate the cursor from sp_cursor_list.CLOSE @ReportDEALLOCATE @ReportGO
-- Close and deallocate the original cursor.CLOSE c1DEALLOCATE c1GO
The result of the call to sp_cursor_list is not very intuitive:
I can easily imagine that you would prefer an output like this:
You can achieve this by writing a wrapper procedure -or table valued function- around the system stored procedure. Here's an example:
/**********************************//* */
/* User-friendly Cursor List Demo *//* */
/**********************************/ USE AdventureWorksGO
/***************//* Preparation *//***************/ CREATE SCHEMA DockOfTheBayGO
/*******************************************//* User-friendly version of sp_cursor_list *//*******************************************/CREATE PROC DockOfTheBay.usp_cursor_listASSET NOCOUNT ON -- Build a table variable to hold the results from sp_cursor_listDECLARE @Cursors TABLE
(
reference_name char(30), cursor_name char(30), cursor_scope tinyint, [status] int, model tinyint, concurrency tinyint, scrollable tinyint, open_status tinyint, cursor_rows decimal(10,0), fetch_status smallint, column_count smallint, row_count decimal(10,0), last_operation tinyint, cursor_handle int)
-- Declare a cursor variable to hold the cursor output variable-- from sp_cursor_list.DECLARE @Report CURSOR
-- Declare variables to hold the fetched values from @ReportDECLARE @reference_name char(30), @cursor_name char(30), @cursor_scope tinyint, @status int, @model tinyint, @concurrency tinyint, @scrollable tinyint, @open_status tinyint, @cursor_rows decimal(10,0), @fetch_status smallint, @column_count smallint, @row_count decimal(10,0), @last_operation tinyint, @cursor_handle int-- Execute sp_cursor_list into the cursor variable.EXEC master.dbo.sp_cursor_list @cursor_return = @Report OUTPUT,@cursor_scope = 3
-- Fetch the first row from the sp_cursor_list output cursor.FETCH NEXT FROM @Report INTO@reference_name,
@cursor_name,
@cursor_scope,
@status,
@model,
@concurrency ,
@scrollable ,
@open_status,
@cursor_rows,
@fetch_status,
@column_count,
@row_count,
@last_operation,
@cursor_handle
WHILE (@@FETCH_STATUS = 0)BEGIN -- Insert the values into the temp table INSERT INTO @Cursors VALUES ( @reference_name,
@cursor_name,
@cursor_scope,
@status,
@model,
@concurrency ,
@scrollable ,
@open_status,
@cursor_rows,
@fetch_status,
@column_count,
@row_count,
@last_operation,
@cursor_handle
)
-- Fetch subsequent rows from @Report FETCH NEXT FROM @Report INTO@reference_name,
@cursor_name,
@cursor_scope,
@status,
@model,
@concurrency ,
@scrollable ,
@open_status,
@cursor_rows,
@fetch_status,
@column_count,
@row_count,
@last_operation,
@cursor_handle
END-- Close and deallocate the cursor from sp_cursor_list.CLOSE @ReportDEALLOCATE @Report-- Return the information from the table variable, translated into readable descriptionsIF (SELECT count(*) FROM @Cursors) > 0
SELECT [Cursor Reference] = reference_name,
[Declared Name] = cursor_name,
[Scope] = CASE cursor_scopeWHEN 1 THEN 'local'
ELSE 'global'
END, [Status] = CASE [status]WHEN 1 THEN 'open'
WHEN 0 THEN 'no rows'
WHEN -1 THEN 'closed'
WHEN -2 THEN 'no reference'
WHEN -3 THEN 'does not exist'
END, [Model] = CASE modelWHEN 1 THEN 'static'
WHEN 2 THEN 'keyset'
WHEN 3 THEN 'dynamic'
WHEN 4 THEN 'fast forward'
END, [Locking] = CASE concurrency WHEN 1 THEN 'read only'
WHEN 2 THEN 'scroll locks'
WHEN 3 THEN 'optimistic'
END, [Scrolling] = CASE scrollable WHEN 0 THEN 'forward only'
WHEN 1 THEN 'scrollable'
END, [Open] = CASE open_status WHEN 0 THEN 'no'
WHEN 1 THEN 'yes'
END,[Qualifying Rows] = cursor_rows,
[Fetch Status] = CASE fetch_status WHEN 0 THEN 'successful'
WHEN -1 THEN 'out of bounds'
WHEN -2 THEN 'missing row'
WHEN -9 THEN 'no fetch done'
END,[Columns] = column_count,
[Rows for last operation] = row_count,
[Last operation] = CASE last_operation WHEN 0 THEN 'none'
WHEN 1 THEN 'OPEN'
WHEN 2 THEN 'FETCH'
WHEN 3 THEN 'INSERT'
WHEN 4 THEN 'UPDATE'
WHEN 5 THEN 'DELETE'
WHEN 6 THEN 'CLOSE'
WHEN 7 THEN 'DEALLOCATE'
END,[Handle] = cursor_handle
FROM @CursorsGO
/*********//* Usage *//*********/-- Declare and open a keyset-driven cursor.DECLARE c1 CURSOR DYNAMIC GLOBAL
FOR SELECT LastName FROM Person.ContactWHERE LastName LIKE 'S%'
ORDER BY LastNameOPEN c1-- Call the user-friendly version.exec DockOfTheBay.usp_cursor_list-- Close and deallocate the original cursor.CLOSE c1DEALLOCATE c1GO
/***********//* Cleanup *//***********/DROP PROC DockOfTheBay.usp_cursor_listDROP SCHEMA DockOfTheBayGO
No comments:
Post a Comment