Transact-SQL: scorrere la lista di cursori utilizzando sp_cursor_list

Ecco un esempio della store procedure sp_cursor_list, che restituisce tutte le informazioni relative ai cursori allocati:

DECLARE C_PIPPO CURSOR KEYSET FOR SELECT * FROM VENDITE
OPEN C_PIPPO

DECLARE C_TOPOLINO CURSOR STATIC FOR SELECT * FROM VENDITE
OPEN C_TOPOLINO

DECLARE C_PAPERINO CURSOR DYNAMIC FOR SELECT * FROM VENDITE
OPEN C_PAPERINO

DECLARE C_PLUTO CURSOR FAST_FORWARD FOR SELECT * FROM VENDITE
OPEN C_PLUTO

DECLARE @CURSORI CURSOR

EXEC master.dbo.sp_cursor_list @cursor_return = @CURSORI OUTPUT, @cursor_scope = 2

FETCH NEXT from @CURSORI

WHILE (@@FETCH_STATUS <> -1)
BEGIN

FETCH NEXT from @CURSORI
END

CLOSE @CURSORI
DEALLOCATE @CURSORI

CLOSE C_PAPERINO
DEALLOCATE C_PAPERINO

CLOSE C_TOPOLINO
DEALLOCATE C_TOPOLINO

CLOSE C_PLUTO
DEALLOCATE C_PLUTO

CLOSE C_PIPPO
DEALLOCATE C_PIPPO

Il risultato:

reference_name	cursor_name	cursor_scope	status	model	concurrency	scrollable
open_status	cursor_row	fetch_status	column_count	row_count	last_operation	cursor_handle
C_PIPPO	C_PIPPO	2	1	1	1	1
1	16	-9	4	0	1	180150003
C_TOPOLINO	C_TOPOLINO	2	1	1	1	1
1	16	-9	4	0	1	180150005
C_PAPERINO	C_PAPERINO	2	1	3	3	1
1	-1	-9	4	0	1	180150007
C_PLUTO	C_PLUTO	2	1	4	1	0
1	-1	-9	4	0	1	180150009