Related to this blogpost, I wanted to log all queries executed on a Microsoft SQL Server database.
This can easily be done by the SQL Server Profiler, which gets shipped with the SQL Studio Express application of Microsoft.
However, after navigating through my webapplication (which uses JPA (Hibernate)), I could only see a bunch of the following SQL statements:
EXEC sp_execute 6, 8 EXEC sp_execute 7, 8 EXEC sp_execute 4, 8
Hibernate is running a prepared query. To get the actual SQL query, you can run the following query (you will still have to look for the query, but, it's getting you a lot closer then sp_execute :-)):
SELECT SQLTEXT.text, STATS.last_execution_time FROM sys.dm_exec_query_stats STATS CROSS APPLY sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT WHERE STATS.last_execution_time > GETDATE()-1 ORDER BY STATS.last_execution_time DESC
By tweaking the WHERE clause, you can easily retrieve the quer(y)(ies) you are looking for :-)!
This is really slick. Anyway to include the user that MADE the query?
Hmm, not really. Need to check :-) ...
Hmm, what about this query:
SELECT c.session_id, s.host_name, s.login_name, s.status, st.text, s.login_time, s.program_name, * FROM sys.dm_exec_connections c INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st ORDER BY c.session_id
#Jochus Thanks for the last query !
Thank you for the last query , this helps ..
Is there a way find out login details for all sql handle as the last query looks for (most_recent_sql_handle) only.