SQL Server Portal

sys.dm_exec_query_stats is very helpful dynamic management view when it comes to query recovery.
Sometimes we write queries to perform some operations in the database and after that close the script window without saving. Later on realize, if only had I saved it, it would have saved some time. Here the solution comes namely sys.dm_exec_query_stats.
This view can recover your query from sql server. Let me create an example to explain it.

First open a new query window and execute the given below script and close the query without saving it.

USE AdventureWorks2012
GO
Select * from Sales.vStoreWithContacts
Select * from Production.ScrapReason
Select * from Sales.vStoreWithAddresses
Select * from Purchasing.vVendorWithContacts
Select * from HumanResources.Shift
Select * from Purchasing.vVendorWithAddresses
Select * from Production.ProductCategory
Select * from Purchasing.ShipMethod
Select * from Production.ProductCostHistory
Select * from Production.ProductDescription
Select * from Sales.ShoppingCartItem

After that, just execute the given below script, it will recover your query.

SELECT Distinct qt.TEXT AS [Queries]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

The reason why we used distinct in the above query is, there are queries that we have executed multiple times that will appear as well.

Still, I am searching for a solution that can link these queries to the user who executed it so the recovery would be easier. Will update you on this in the upcoming post.

Reference : MSDN