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 :-) ...
some investigation? I need to know, the user that made the query
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
How to get name of database executed.
#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.
No, sorry, not that I am aware of.
Great query, thanks for the help! in case that I don't want to see most_recent_sql_handle, and I want to see all the sqltext executed, filtered by dates, how I can modify the query to do this? thanks in advance for your support.