asked
last Altered Column
Hi, Is there is any way in Sql Server2005 to know when the last time column was modified or changed ??? or How to find the date and time of last updated column in a table? Any help is really appreciated....
Short answer? No. There isn't any way to track the modification date/time of individual columns. Edit -> Thought I should point out that my answer was with respect to schema changes, and I am talking about the 'default configuration'. You can set up DDL triggers to track schema changes if you want, no problem, but if you are looking at an existing database without those and you want to know what column was modified last, then you're up a certain creek.
In sql server there is no way to find it unless you have implemented some kind of custom change tracking or have C2 auditing enabled on the server. In SQL Server 2008 you can use the Change Tracking system build into the SQL Server. ([Change Tracking Overview][1]) But for all, you are able to track changes only to the point when the change tracking history starts. [1]:
http://msdn.microsoft.com/en-us/library/bb933875.aspx
Can I ask how you would use Change Tracking to provide that? I understand that you could use Change Tracking to track value changes in a column, but if you changed the datatype from varchar(20) to varchar(30) (for example) - how would you derive that using change tracking? Actually, typing that has led me to an interesting observation - I read it as schema change, and not data change. Interesting! :)
There is no note of DDL changes. In case of DDL changes on SQL 2005 and above a DDL Triggers can be created to log the DDL Changes. In case of DDL changes on the SQL 2000 the only possible way probably will be the mentioned C2 auditing, but this will produce enormous trace data.
Gotcha :) What a long day! Anyway, if you hadn't noticed previously, +1...
There is no way to do it without advance prepration. As Pavel pointed out, you could do this using the change tracking system in 2008. In 2005 you could write a trigger that would track that information, but doing so would add complexity and come with a performance price, not to mention the awkwardness that comes with the "hidden code" of a trigger.
Based on Matt's comments to Pavel, I'll add that I am assume we are talking about data change, not schema change here.
