Sometimes we do R&D on the query and try to found the solution to any problem. Due to some reason, you shut down your system without saving the query, oops now you want to recover your R&D query, so using the following query, you can get back the query text.
sys.dm_exec_query_stats and sys.dm_exec_sql_text catalog the view records for all currently executed query using the following query we can find the recently executed query.
SELECT dest.TEXT AS [MyQuery], a.last_execution_time AS [DateTime]
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS dest
WHERE a.last_execution_time>dateadd(day,-1,getdate())
ORDER BY a.last_execution_time DESC
|
For a live example, run the following query and, after that, execute the above query to find the recently executed query.
CREATE TABLE EMP
(
ID INT,
NAME VARCHAR(100),
ADDRESS VARCHAR(1000),
DOB VARCHAR(20)
)
INSERT INTO EMP(ID,NAME,ADDRESS,DOB)
VALUES(1,'Ashish','Delhi','10-12-2012')
SELECT * FROM EMP
|
Result