last five update statement on table or view

Question

last five update statement on table or view

Shambhu Rai 1,411

Hi Expert,

how to get last five update statement fired on table or view in sql server

update table1 set col1=

update table1 set col2=

update table1 set col3=

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Oury Ba-MSFT 21,151 Reputation points Microsoft Employee Moderator

2023-06-13T17:34:37.2166667+00:00

@Shambhu Rai

Do you have any update statements fired on the table or view recently.?

Regards,

Oury
Shambhu Rai 1,411 Reputation points

2023-06-13T17:37:24.5733333+00:00

have replied you there is no rows reflected
Shambhu Rai 1,411 Reputation points

2023-06-13T17:40:50.3766667+00:00

any other suggestion please

Oury Ba-MSFT 21,151 Microsoft Employee Moderator

@Shambhu Rai

I have tried the below and it worked for me.

Please run this against your master DB

SELECT top 5 deqs.last_execution_time AS [lastExecutedTime],
       dest.TEXT AS [QueryText]
FROM   sys.dm_exec_query_stats AS deqs
       CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE  dest.text LIKE 'update%'
ORDER  BY deqs.last_execution_time DESC;

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Olaf Helper 47,616 Reputation points

2023-06-14T09:06:51.78+00:00

any other suggestion please

Implement an auditing / CDC, there is no other way to get the informations

4 answers

Your answer

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Oury Ba-MSFT 21,151 Reputation points Microsoft Employee Moderator

2023-06-13T17:34:37.2166667+00:00

@Shambhu Rai

Do you have any update statements fired on the table or view recently.?

Regards,

Oury
Shambhu Rai 1,411 Reputation points

2023-06-13T17:37:24.5733333+00:00

have replied you there is no rows reflected
Shambhu Rai 1,411 Reputation points

2023-06-13T17:40:50.3766667+00:00

any other suggestion please
Oury Ba-MSFT 21,151 Reputation points Microsoft Employee Moderator

2023-06-13T19:14:58.84+00:00

@Shambhu Rai

I have tried the below and it worked for me.

Please run this against your master DB

SELECT top 5 deqs.last_execution_time AS [lastExecutedTime], dest.TEXT AS [QueryText] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.text LIKE 'update%' ORDER BY deqs.last_execution_time DESC;
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Olaf Helper 47,616 Reputation points

2023-06-14T09:06:51.78+00:00

any other suggestion please

Implement an auditing / CDC, there is no other way to get the informations

Answer 1

i modified the query that Oury has shared with you (assuming you have configured auditing on SQL server)

maybe this works for you

USE master;

SELECT deqs.last_execution_time AS [Time],
       dest.TEXT                AS [Query]
FROM   sys.dm_exec_query_stats AS deqs
       CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE  dest.text LIKE '%UPDATE%your_table_name%'
ORDER  BY deqs.last_execution_time DESC

the reference document for your question is as follows

https://dba.stackexchange.com/questions/36149/how-to-find-delete-and-update-statements-that-have-been-executed-on-a-database

Answer 2

Erland Sommarskog 133K MVP Volunteer Moderator

To get this information accurately, you need to set up the appropriate auditing. There is nothing built-in for this. The query that Oury shared gives some information, but is not accurate.

Answer 3

Olaf Helper 47,616

how to get last five update statement fired on table or view in sql server

If you haven't implemented an auditing, then you can not get the informations.

Answer 4

Shambhu Rai 1,411

any other suggestion please

Oury Ba-MSFT 21,151 Microsoft Employee Moderator

@Shambhu Rai

I have tried the below and it worked for me.

Please run this against your master DB


Copy
SELECT top 5 deqs.last_execution_time AS [lastExecutedTime],
       dest.TEXT AS [QueryText]
FROM   sys.dm_exec_query_stats AS deqs
       CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE  dest.text LIKE 'update%'
ORDER  BY deqs.last_execution_time DESC;

LiHongMSFT-4306 31,621 Reputation points

2023-06-14T02:38:49.58+00:00

This query will not return all update statements. For example, update with CTE.

Share via

last five update statement on table or view

4 answers

Your answer