How to track cursor statements separately in SQL Server extended events?

Question

I want to track statements into SQL Server cursor using extended events but all solutions finding in internet does not work - for example, this.

Test code:

DECLARE @objectId int;

DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT TOP(3) object_id
FROM sys.tables AS t
ORDER BY object_id;

OPEN cur;
FETCH NEXT FROM cur INTO @objectId;
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRAN;
    SELECT @objectId AS object_id;
    WAITFOR DELAY '00:00:01';
    COMMIT;
    FETCH NEXT FROM cur INTO @objectId;
END;
CLOSE cur;
DEALLOCATE cur;

Answer 1

If you wish to track SQL Server cursors and their inner statements by using extended events, you have these events to choose from the library:

1. sqlserver.cursor_manager_* events
   Use them for capturing TSQL Cursor

2. sqlserver.cursor_* events
   Use them for capturing API Cursor

In your example, you are using the TSQL cursor.

---

Here is an example Extended Events Session script to capture TSQL Cursor open/close events.
It will capture events fired by the TSQL cursor declared in your test code.

NOTE: I also had to capture event sqlserver.sql_statement_completed, in order to get the statement executed from within the cursor.

CREATE EVENT SESSION [Track_tsql_cursor] ON SERVER 
ADD EVENT sqlserver.cursor_manager_cursor_end( 
    ACTION( 
        sqlserver.client_app_name, 
        sqlserver.database_name, 
        sqlserver.is_system, 
        sqlserver.session_id, 
        sqlserver.sql_text) 
    WHERE ([sqlserver].[database_name]=N'...') 
), 
ADD EVENT sqlserver.cursor_manager_cursor_begin( 
    ACTION( 
        sqlserver.client_app_name, 
        sqlserver.database_name, 
        sqlserver.is_system, 
        sqlserver.query_hash, 
        sqlserver.session_id, 
        sqlserver.sql_text) 
    WHERE ([sqlserver].[database_name]=N'...') 
), 
ADD EVENT sqlserver.sql_statement_completed( 
    ACTION( 
        sqlserver.client_app_name, 
        sqlserver.database_name, 
        sqlserver.is_system, 
        sqlserver.query_hash, 
        sqlserver.session_id, 
        sqlserver.sql_text) 
    WHERE ([sqlserver].[database_name]=N'...') 
) 
WITH ( 
    MAX_MEMORY=16384 KB, 
    MAX_DISPATCH_LATENCY=10 SECONDS, 
    TRACK_CAUSALITY=ON 
); 
GO 

Here are the results from your test query (tested on SQL Server 2019 and 2014):