How do I tell if a table structure was changed?

[TheBugSlayer]

Hi all. I need a quick way to tell if a table's structure was changed in SQL Server 2005. I had seen a query before but cannot remember where. Right now I am building a query which uses sys.columns and sys.tables and addint to is as I go along.

Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[SQLBill]

Check out sys.objects table in each database. It has a column 'modify_date' that shows when the object was last modified. Join it to the sys.tables and/or the sys.columns table and that might do what you want.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[TheBugSlayer]

Thanks Bill. Both sys.objects and sys.tables keep the create and modify date for the table. This being a SQL Server 2005 database I reckon one will have to implement his own change data capture for column changes. This is my query.

SELECT T.Name, T.create_date, T.modify_date, C.name 
FROM sys.tables T
JOIN sys.columns C ON C.object_id = T.object_id
WHERE T.name IN ('Table1', 'Table2')
ORDER BY T.name, C.name

Thanks again for taking the time to answer.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)