modification date

[michelleqw]

Dear sqlserver users,

In the enterprise manager we are looking to the stored procedures / tables etc, we always see a column with the creating date. This date can be very interesting but more important is the modify date.

Is there any possibility to get this date and if yes how do we do that.

Nice regards,

Michelle.

 

[kavin]

As Far as my knowledge there is no such modify date upto SQL 2000.

 

[michelleqw]

Thanks I was afraid of that.

Michelle.

 

[dbomrrsm]

ther is a column somewhere in one of the system tables which has a column called something like last modified date - cant remember where it is though or whether its what you are looking for.

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

Run this in QA in the DB you are interested in:

select * from INFORMATION_SCHEMA.routines

Then look to the far right - last column of the results which is a column called LAST_ALTERED - but again not sure if its what you want.


[blue]DBomrrsm[/blue]

 

[SQLSister]

If you want the date the data was last modified, you need to program the functionality in. I suggest an update trigger which puts the current date into a ModifiedDate field every time the record is updated. It probably is also a good idea if you are going to do this to add a ModifiedBy field and put the SYSTEM_USER value into that field inthe same trigger.

Or you could consider if you need audit tables to track the actual changes and the former values.

Questions about posting. See faq183-874

 

[vongrunt]

Then look to the far right - last column of the results which is a column called LAST_ALTERED - but again not sure if its what you want.

Unfortunately this column is mapped to sysobjects.crdate. If you ALTER PROCEDURE/FUNCTION, it's value won't be changed.