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.

8 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."

haiyan said...

The Requiem shows up so many cool pictures that got me thinking a lot. And I really mean that a lot of requiem gold is very important and required. First off if the name is not taken the name and some requiem lant of book Requiem I do not if it is allowed to name a book. Some requiem money will well enough chatting better go and write this book. Comment on this please should I write this book or not. See you. In Requiem, there is blood. Monsters, blood, bigger monsters, and yes, more blood and need cheap requiem lant. You know what that requiem online gold means; there are factions, races changing sides, and all out war.

products said...

China Wholesale has been described as the world’s factory. This phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesalers “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and buy products wholesaleChina Wholesale will skyroket your profits.wedding dressescheap naruto cosplayanime cosplay

products said...

Women’s nike tn Shox Rivalry est le modèle féminin le plus tendance de baskets pour le sport. tn chaussuresConcernant la semelle :spyder jacketsCheap Brand Jeans Shop - True Religion Jeans cheap nike shox & Puma Shoes Online- tn nike,Diesel Jeans le caoutchouc extérieur, l’EVA intermédiaire Levis Jeanset le textile intérieur s’associent pour attribuer à la.ed hardy shirts pretty fitCharlestoncheap columbia jackets. turned a pair of double plays to do the trick.Lacoste Polo Shirts, puma basket, Burberry Polo Shirts.wholesale Lacoste polo shirts and cheap polo shirtswith great price.Thank you so much!!cheap polo shirts men'ssweate,gillette mach3 razor bladesfor men.As for Cheap Evisu JeansCheap Armani Jeanspolo shirtsPuma shoes

Replica Watches said...

29047126483369175 I play dofus Replica Watches for one year, I Replica Rolex Watches want to get some Replica Watch kamas to buy Replica Chanel Watches item for my character. So, I search "Replica Swiss Watches" on google and found many website. As Exact Replica Graham Watch the tips from the forum, I just review the Swiss Replica Watches websites and choose some Replica Montblanc Watches quality sites to Replica Cartier Watches compare the price, and go to their Replica Breguet Watches online support to make Replica Breitling Watches the test. And Last Chaos Gold I decide to use Replica BRM Watch at the end. And Tag Heuer Replica Watch that is the Replica IWC Watch beginning..

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!