Bradley is a consultant at Pragmatic Works in Jacksonville, FL. He was an author on the book SharePoint 2010 Business Intelligence 24-Hour Trainer and tech edited the SQL 2011 Bible. His experience on the Microsoft BI platform includes DTS, SSIS, SSRS, SSAS and migrations and conversions. He has helped numerous companies in successfully developing and implementing new business intelligence solutions into their organizations. Bradley also participates as a speaker in community events like SQL Saturday, Code Camp, SQL Lunch and SQL Server User Groups. He is a contributor on sites such as BIDN.com and SQL Server Central as well as an active member of the Jacksonville SQL Server User Group (JSSUG).
If you have ever closed a query window by accident only to wish you could get back your work an instant later you may be in luck. If you ran the query in question there is hope for you. The query below will return queries that have been run for you including the time and query text. I haven’t figured up a way to get the user that executed the query just yet. So if you have that tidbit to add to this so you can filter a little easier please let us know.
You can narrow the list by adding a where clause and searching for some text you know was in the query or looking around a specific time period. The text wont be formatted pretty when you copy it, but reformatting is better than rewriting!
Without further ado, here is the magic:
a.last_execution_time AS ExectuionTime,
b.text AS Query
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
ORDER BY a.last_execution_time DESC