Imagine a situation when you are working on a query for couple of hours and suddenly your management studio is crashed or your PC is restarted and you forgot to hit the “CTRL + S” in between. Worst nightmare of any developer. It is always extrmely difficult to re-create the same query again.
In the past I used to go to this folder to find my unsaved sql scripts : C:\Users\YourUsername\AppData\Local\Temp
But somehow this was never realiable. There were few occassions when I didn’t find the query I was looking for. Few days back I came across this table valued system fuction sys.dm_exec_sql_text which can save our day from the nightmare of not saving the scripts. More information about this function can be found on the Books Online here: sys.dm_exec_sql_text
Now striaght away jump to the query which can help us to recover the unsaved SQL query scripts from the management studio.
--------------------------------------------------------------------------------------------------------
USE [DATABASE_NAME]
SELECT S.LAST_EXECUTION_TIME
,T.TEXT AS [SCRIPT]
FROM SYS.DM_EXEC_QUERY_STATS AS S
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(S.SQL_HANDLE) AS T
--WHERE T.TEXT LIKE '%SOMETHING%'
ORDER BY S.LAST_EXECUTION_TIME DESC
--------------------------------------------------------------------------------------------------------
Few notes:
1. You will need VIEW SERVER STATE permissions to execute this query.
2. Since there could be many queries running on the server you would like to use WHERE condition TEXT LIKE '%SOMETHING%'.
3. You can recover the queries only till SQL Server keeps them in the memory.
4. DM_EXEC_QUERY_STATS has few more useful columns that can help you with some extra information related to your query. You may like to add them in this query.
Thanks
Mangal
