Solution:

Update for table is applicable in 2 ways

1. Structure Changes

2. Data changes

first we will check the schema changes

use the below script for this

USE AdventureWorks12

GO

CREATE TABLE Test123

(ID INT,

COL VARCHAR(100))

GO

INSERT INTO Test123

SELECT 1,’First’

UNION ALL

SELECT 2,’Second’

GO

select * from Test123

Result:

if you modify the structure of table i.e using DDL command or GUI, we can see the modified date information using below query

select modify_date,* from sys.objects where type=’u’ and name=’Test123′

if the data is updated or inserted or deleted in the table and table has no lastupdated column to track the information , how we can get those information

below is the example for this

create table testupdate(id int ,name varchar(10))

INSERT INTO testupdate

SELECT 1,’First’

UNION ALL

SELECT 2,’Second’

GO

select * from testupdate

SELECT GETDATE()

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( ‘AdventureWorks2012’)

AND OBJECT_ID=OBJECT_ID(‘testupdate’)

Screen shots

Please check and let us know if you have any concerns on this.

Thanks for viewing this.

Regards,

Chaitanya

Visit site: http://www.sqlblogging.com

Send an Email: sqlblogging