SQL Server find previously run queries

Have you ever created a query, exported the results and then closed the query tab, not saving because it was just a one time query?  Then 5 minutes later you get a call saying, “oh, can you run it again with these values?”.  Now you have to wrack your brain and figure out what you previously created.

Here is a quick example how to get your query information.  For example I wrote the query below because one of the accountants requested some information.  I export the data out and then close my query tab.

Five minutes later, they call back and say they need additional items run, can you send the exact columns you sent before?  Uh Oh…what did I run before?

With SQL Server you can query some SQL Server items and retrieve the exact query that you ran so you do not need to recreate it.  This information is stored in the Cached Query Plans in SQL Server, so there is a chance of it being aged out if there is memory pressure.

The items you will need are:  sys.dm_exec_query_stats  and sys.dm_exec_sql_text

Here are their links for more information: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sql-text-transact-sql?view=sql-server-2017

I modified the Microsoft query and now you can specify what table you think the query used and it will show you any query in the cache that matches.

In the WHERE clause below, I am looking for any SELECT statement that used the CI_PAY table.

SELECT qs.last_execution_time,
qt.text,
qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid, 
qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE qt.text like '%SELECT%CI_PAY%' 
ORDER BY qs.last_execution_time DESC;

Just for fun, this what the sys.dm_exec_query_stats view looks like.

And this is what happens if you pass the above SQL_HANDLE into sys.dm_exec_sql_text.  The query above combines the process, so you do not have to look at each item individually.