SQL Server shows the method for unreleased cursors. SQL Server releases cursors.

SQL Server shows the method for unreleased cursors. SQL Server releases cursors.

SQL server cursors have never caught a cold and seldom use cursors in SQL Server. A netizen asked me a few days ago how to check that no released cursors exist in the database, I tested and verified it. By the way, I sorted it out here.

Session 1: We simulate an application or script. After opening the cursor, we forget to close or release the cursor.

DECLARE Cursor_Test CURSOR FORSELECT * FROM dbo.TEST;OPEN Cursor_Test; 

In this case, how do we check that no released cursor exists in the database? In fact, SQL SERVER provides a dynamic management function sys. dm_exec_cursors, which returns information about opened cursors in various databases.

SELECT * FROM sys.dm_exec_cursors(0) 

For more information about fields in the table returned by the dynamic management function, see the MSDN documentation. Different database versions are different. If you want to query a cursor that is not closed, you need to filter the is_open field (specify whether the cursor is open. 1 indicates opening, 0 indicates closing)

Session 2: view the cursor that is not closed

SELECT session_id , cursor_id , name , creation_time , is_openFROM sys.dm_exec_cursors(0)WHERE is_open = 1; 

If you want to view the cursor that has been closed but has not been released, you can execute the CLOSE cursor command in session 1 to CLOSE Cursor_Test; but do not release the cursor, as shown below:

In addition, you can use the following SQL statement to view the cursor information on the server that has been opened for more than the specified time (1 hour), and adjust the query conditions as needed.

SELECT creation_time ,cursor_id ,name ,c.session_id ,login_name ,c.creation_time ,c.is_openFROM sys.dm_exec_cursors (0) AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_idWHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 1;GO

The preceding section describes how to view unreleased cursors on SQL Server. I hope this will be helpful to you!

Articles you may be interested in:

• Simple SQL Server cursor example
• How to share SQL cursor instances
• Usage of mysql Stored Procedure cursor Loop
• In-depth analysis on usage and closure of Mysql cursors
• SQL Server traverses the two methods recorded in the table (using Table variables and cursors)
• SQL Server cursor usage/closure/release/optimization Summary
• SQL Server cursor statement Declaration/Open/loop instance
• Detailed usage of MySQL-based cursors