When you alter view or stored procedure and you realize, previous version wasn't saved and you need it. Or you want recover SQL script immediately after management studio crash and you were lucky, that you run it at least once. There is a nice tricky way how do to it by SQL script without any 3rd party enhancement.
All you need is persmission for master database and run this script, which using aggregate performance statistics table with sql handle table
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
For easy way to find specific SQL code you can search based on text inside script
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
WHERE execsql.text LIKE '%TEXT_TO_FASTER_FIND_RIGHT_SQL%'
ORDER BY execquery.last_execution_time DESC
Obviously this works only for scripts you have already run, so they were saved to statistic table. This can save you a lot of unnecessary work.
