Have you ever wondered what SQL query was last executed by your users across all SQL Server databases on your server? I have seen some solutions on the internet that use the sysprocesses view to retrieve this information. In this post, I will show you how this information can be retrieved better using Dynamic Management Views.
Please use this query:
SELECT conn.session_id, sson.host_name, sson.login_name, sqltxt.text, sson.login_time, sson.status FROM sys.dm_exec_connections conn INNER JOIN sys.dm_exec_sessions sson ON conn.session_id = sson.session_id CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt ORDER BY conn.session_id
Here I have utilized the sys.dm_exec_connections Dynamic Management View, in conjunction with the sys.dm_exec_sessions DMV and sys.dm_exec_sql_text Dynamic Management Function (DMF) to return the last query executed against all SQL Server databases, in that server.
Here’s a quick overview of what these DMV’s and DMF do
sys.dm_exec_connections - Returns information about the connections established to this instance of SQL Server and the details of each connection
sys.dm_exec_sessions - Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more
sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle
In the last statement, we are passing the value contained in the most_recent_sql_handle column of this DMV to the sys.dm_exec_sql_text DMF. The DMF returns the text of the sql query, whose sql_handle we passed to it. This sql_handle that we passed, uniquely identifies the query.
Here’s the output