how to find out last updated row in sql ?

Kavi Nithyasri

10y1.1kSQL

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

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajeesh Menoth10y ago
Accepted 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
andlast_user_update is not null
orderby 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
0
Vasanth Natarajan10y ago
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
0
Raja T10y ago
HiMidhun Tp, No,
In that query last_user_update,object_id,object_name etc in given Database and Table name.
0
Kavi Nithyasri10y ago
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.
0
Midhun Tp10y ago
Hi Raja,
With that query can we get last updated row?
0
Raja T10y ago
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
0
John10y ago
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
0
Midhun Tp10y ago
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.
0