Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch



/*
You then run the following query against sys.dm_exec_connections and see if that turns up anything 
useful based on the most recent SQL handle:

SELECT t.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t
WHERE session_id = 53

This returns:

FETCH API_CURSOR0000000000000004

Didn’t help.  

So what about other DMVs?  You eventually find a reference to the sys.dm_exec_cursors DMV and see it 
can tell you about open cursors, their properties and associated SQL handle.  But you're not sure the 
SQL Handle will be any help because it hasn't been helpful with the other DMVs:

*/
SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (125) c
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t



--A. Detecting old cursors
--This example returns information about cursors that have been open on the server
-- longer than the specified time of 36 hours.



SELECT creation_time, cursor_id, name, c.session_id, login_name 
FROM sys.dm_exec_cursors(0) AS c 
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id 

WHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 48;