Do you find yourself in the situation where you want to audit or check what is happening with your server in terms of queries? Or maybe you are in a more unpleasant situation and you need to recover a query after a SQL Server crash or a server crash. Either way, the following script will come in handy.
The focus word here is “ran”, meaning that you can recover only queries that ran at least once. Most often situation that I get myself into is when developing stored procedures: I comment out the header part (with CREATE PROCEDURE) and I write my code checking the result from time to time. It doesn’t happen too often, but I saw it happening a few times – SSMS crashes and I didn’t get the chance to save my script or compile the procedure. And no, I don’t want to start over. 🙁
So here’s how to do it to recover you last ran queries:
SELECT [q].[last_execution_time] AS [LastExecutionTime]
,[s].[text] AS [Script]
FROM sys.dm_exec_query_stats AS [q]
CROSS APPLY sys.dm_exec_sql_text([q].[sql_handle]) AS [s]
ORDER BY [q].[last_execution_time] DESC