CODEWITHSUNDEEP
sqlt-sqlstored-proceduressystem-stored-procedures
Hardryv
Hardryv

Reputation: 793

retrieve most recently executed SQL command (T-SQL)

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?

Upvotes: 7

Views: 4852

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294207

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.

Upvotes: 4

Sadegh
Sadegh

Reputation: 6854

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

Upvotes: 20

John Saunders
John Saunders

Reputation: 161773

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.

Upvotes: 1

Related Questions