Retrieve Unsaved SQL Query Scripts

Introduction

In this article, we will have a look how to retrieve the Unsaved SQL Query using T-SQL. Sometimes, we may close the Query Window or SSMS accidentally without saving. This may cause to loss the Queries which we where working.

Retrieve Unsaved SQL Query using T-SQL Statement

Apply the below T-SQL Statement in which DB we want to retrieve the unsaved queries. It provides a list of scripts and its time of execution in the last 24 hours

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

When the above statement is executed, we will get the Output as shown below,

Fig.1 List of Unsaved Scripts

Conclusion

In this article, I have shared a simple tip to retrieve the unsaved scripts in SQL Server using T-SQL. I hope this article helped you to know something new. Please share your feedback in the comment section.

Consider reading other SQL articles of Mine

• Select Statement Execution Order In SQL Server
• System Databases in SQL Server
• MS SQL Server – Zero to Hero Query Master – Part 4
• Schema Comparisons using Visual Studio SQL Data Tools
• Link a SQL Server Database Project to a Git Repository
• Create and Publish SQL Server Database Project With Visual Studio
• Customize Azure Data Studio with Dashboard Widgets
• Cycle Clipboard Ring In SSMS – Reuse Copied Items
• Set custom colors to differentiate between environments in SSMS
• Recover unsaved SQL queries in SSMS
• Multiple Backup Files of the SQL Server database with SSMS and T-SQL
• Difference between CURRENT_TIMESTAMP vs GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server