cursors status ~ vijayanandgawle

Wednesday, June 9, 2010

cursors status

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

June 09, 2010 sql server 0

0 comments: