Table schema modified date

[Tekreader]

Hi
Can anyone tell me How to find the table schema(definition of table) changed date in Sql server?
ie When any user alter a atble want to find when it was altered.

I know cdate in sysobjects will not update the value to show the date.

Thanks
Tekreader

 

[SQLSister]

Which version of SQL server are you using? I know this cannot be done in SQl Server 2000 but it may be possible with 2005.

"NOTHING is more important in a database than integrity." ESquared

 

[Tekreader]

Hi SqlSister,
I want to find this in Sql 2000.If not possible at all,Let me know how to find in sql 2005.

Thanks a lot for the Reply

 

[TheSQL]

In SQL 2005 the syntax would be:

SELECT [name] TblName, modify_date
FROM    sys.tables

SQLSister is right. It can't be done in 2000, unless I'm grossly mistaken.

 

[SQLSister]

It would be best to implement a security model where no users are allowed to change table structures. On production, only a very small set of people who are database experts should have rights to change the tables and they should document their work and only make changes through scripts which have been tested and reviewed. Even developers do not have rights to change production table structures in our databases. If someone has changed your database without the change being known or approved in advance, it is time to implement a better security model. What's past is past, you will not be able to find out who made the change at this point in time. All you can do is fix the process so that only a very limited number of senior, experienced people can make a change. If this is something you must know for future events, I suggest an immediate upgrade to SQL 2005.

"NOTHING is more important in a database than integrity." ESquared