how to find out last updated row in sql ?

Question

find out the last modified record in sql without identity constraint ?

Rajeesh Menoth · Answer

Hi, last_user_update column will mention the time. Try this code : declare @objectid int select @objectid=object_id from sys.objects where name = 'YOURTABLENAME' select top 1 * from sys.dm_db_index_usage_stats where object_id=@objectid and last_user_update is not null order by last_user_update Reference : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a0742b7-b976-42b7-8230-4ae46441a63a/when-was-the-last-time-a-record-was-addedmodified-in-a-sql-table?forum=transactsql

Vasanth Natarajan · Answer

Hi Try this... Select Max(Your_Table_PK) from Your Table; or if u have update system datetime to the Table Select Max(updated_datetime) from your Table; But i suggest don't use the Top or limit in your table

Raja T · Answer

Hi Midhun Tp , No, In that query last_user_update,object_id,object_name etc in given Database and Table name.

Kavi Nithyasri · Answer

Thanks to every one. Finally i understood that it is not possible to get last modified record value , instead we can get last modified time only.

Midhun Tp · Answer

Hi Raja, With that query can we get last updated row?

Raja T · Answer

Hi, In your table structure any field is available created date /modified date ? or check it like SELECT OBJECT_NAME ( OBJECT_ID ) AS DatabaseName , last_user_update ,* FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID ( 'AdventureWorks' ) --Here ur db name AND OBJECT_ID = OBJECT_ID ( 'test' ) --Here ur table name more details check out below links http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/ https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a0742b7-b976-42b7-8230-4ae46441a63a/when-was-the-last-time-a-record-was-addedmodified-in-a-sql-table?forum=transactsql

John · Answer

If you want to know when a record was last update, you should maintain a lastupdate column is table and update the value with current datetime everytime the record is updated

Midhun Tp · Answer

Hi, I don't think there is any query available for fetching last modified record. Instead you can maintain a column for saving last updated time.

how to find out last updated row in sql ?

Answers (8)