Recovering an accidentally closed query (provided you ran it)

Peter Schmitz

Administrator
Staff member
The other day I was slightly over-enthusiastic when closing tabs, and I ended up closing the tab I had been working in. Several hours of work lost, apparently, as because I had not saved my work, SSMS did not create an auto-save file for me.

Luckily, though, I had been executing the query as I was developing it, and that proved to be my rescue. As it turns out, SQL Server keeps track of queries that got executed, and you can pull the info from the system tables using the following query:

SQL:
Use <database>
SELECT 
    execquery.last_execution_time AS [Date Time]
    , execsql.text AS [Script] 
FROM 
    sys.dm_exec_query_stats AS execquery
CROSS APPLY 
    sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY 
    execquery.last_execution_time DESC
 
Top