retrieve most recently executed SQL command (T-SQL)

Question

One of my developers working on a trigger-based logging facility in SQL Server 2008 asked me if there was a command to retrieve the most recently executed SQL command within T-SQL. I thought there was a system stored procedure for just such a function, but it's possible I'm thinking of another product from a prior decade... online searches yielded us no results.

Does anyone have information on anything of the sort?

Answer 1

What does "most recent" mean in the context of a multi-core machine?

Also, does he mean the most recently started, or the most recently finished?

Finally, he should just open SSMS and look at Activity Monitor.

Answer 2

sure try this :

SELECT
DMExQryStats.last_execution_time AS [Executed At],
DMExSQLTxt.text AS [Query]
FROM
sys.dm_exec_query_stats AS DMExQryStats
CROSS APPLY
sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt
ORDER BY
DMExQryStats.last_execution_time DESC

it will returns recently executed queries along with the date and time at which they were executed

Answer 3

Well, the procedure that retrieves the most current SQL batch can safely return itself :)

On a serious note, you can look at sys.dm_exec_query_stats and sys.dm_exec_procedure_stats to see when a plan was last time executed, based on the last_execution_time column. But note that the method is not reliable because it does not account for plans that were evicted from the cache after execution.