asked
Last executed insert , update or delete query
How to find last executed insert, update or delete query in SQL Server 2008.
Thnx Kev, But it doesn’t work insert into temp(id,name) values(1,'s') select * from sys.dm_exec_connections decs CROSS APPLY sys.dm_exec_sql_text(decs.most_recent_sql_handle) dest where (dest.[TEXT] like '%INSERT%' or dest.[TEXT] like '%UPDATE%' or dest.[TEXT] like '%DELETE%' ) and decs.session_id <> @@SPID
remove the last where clause - that is ignoring this session - I didn't think you would generally be looking for executions within the same query window. Alternatively, execute the insert in another window
One option is to query the Dynamic Management Views. This snippet assumes that SQL hasn't been restarted, that the connection that executed is still alive, and that we want to ignore the connection executing this code.. select * from sys.dm_exec_connections decs CROSS APPLY sys.dm_exec_sql_text(decs.most_recent_sql_handle) dest where (dest.[TEXT] like '%INSERT%' or dest.[TEXT] like '%UPDATE%' or dest.[TEXT] like '%DELETE%' ) and decs.session_id @@SPID Ideally you wouldn't use `select *`, but choose the columns from the DMVs that you are interested in.
The best way to do this is to setup a server side trace or monitor with extended events. Anything else is going to be guessing. Kev's answer is great if you meet all the requirements, but most systems will put the connection back into the connection pool as soon as the query is finished and you won't see what's needed. You could try running a query against sys.dm_exec_query_stats and order by the last_execution_time. Assuming the query is still in cache, you'll see the most recent query, regardless of what it is, the last time it was executed.
