Cursor statuses
Here are the queries that findout the cursors based on their statuses
1 Find out the cursors that are allocated but not opened or closed
--Method 1
select name from sys.dm_exec_cursors(0) where is_open =0
--Method 2
select
cur.cursor_name
from
sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle
where
cur.open_status =0
2 Find out the cursors that are opened and not closed
--Method 1
select name from sys.dm_exec_cursors(0) where is_open =1
--Method 2
select
cur.cursor_name
from
sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle
where
cur.open_status =1
3 Find out the cursors that are allocated but not deallocated
--Method 1
select
name from sys.dm_exec_cursors(0)
--Method 2
select
cur.cursor_name
from
sys.syscursorrefs as ref inner join sys.syscursors as cur on ref.cursor_handl=cur.cursor_handle