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]) But for all, you are able to track changes only to the point when the change tracking history starts. :
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.