Monday, August 6, 2007

Object's modified date

There are lots of businesses have already upgraded to SQL Server 2005.At this time I'd like to take a look at great feature that we have in SQL Server 2005. I have been asked so many times how to know WHEN does the object change? It is a headache in SQL Server 2000. Fortunately, now we can answer that question.

Let's say the user changed number of stored procedures.Using the following statement we can easily get the data.

SELECT name,modify_date FROM sys.procedures
ORDER BY modify_date desc

But let's take it a little bit futher. I'd like to know when does user update the table last time?

CREATE TABLE dbo.uri(c INT NOT NULL PRIMARY KEY)
GO
INSERT INTOdbo.uri VALUES(10)
--we got one entry

SELECT object_name(object_id) AS name ,last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id( 'demo' )

---try to change the data
UPDATE dbo.uri SET c =50 WHERE c =10

--you'll see that last_user_update is changed

SELECT object_name(object_id) AS name ,last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id( 'demo' )

So ,you can play with these Dynamic Management Views to obtain more info.

3 comments:

Leif Haraldsson said...

But what about sys.columns? There you do not have any date, CreateDate and UpdateDate??!!

Uri Dimant said...

Hi Leif
Do you mean if someone has change only data per column?

Razvan Socol said...

Using sys.dm_db_index_usage_stats for this task is not a very reliable way, because the information presented in this DMV is not persisted in the database and therefore does not survive a restart of the server. Books Online says: "The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed."