Way to tell when table last modified

[sqlcasey]

Hi,

Is there a system table or sp that I can use to determine if any SCHEMA (ddl) changes have occurred in any tables in a given database??

Thank you

 

[SQLDenis]

In SQL 2005

select name,create_date,modify_date from sys.tables
select name,create_date,modify_date from sys.procedures

For SQL 2000
If you don't use version control now is the time to start since you can't query that info



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[sqlcasey]

Hi,

I ran the query against sys.tables, and see that 37 tables have "changed" in the past month. However, it seems EXTREMELY unlikely that this is the case, given our environment here.

For example, one of the tables that supposedly was modified, has a modify_date of Saturday at midnight. No one here made any changes at that time.

In BOL, it *does* say that modify_date represents the time when the table was last modified by an ALTER stmt. But I just don't see how this is possible.

Is there any other possible explanation for the modify_date being changed?

Thanks

 

[SQLDenis]

>>ALTER stmt. But I just don't see how this is possible.


maybe ALTER INDEX
statement? Do you have maintainance jobs running at that time


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[sqlcasey]

Yes, that's it... my weekly index de-frag script. But I wish this didn't "qualify", when you rebuild an index, since the table structure does not change, per se. But yes, it is a DDL stmt. Boo.