In a recent post, I demonstrated how the sys.procedures catalog view could be used to determine the date and time that a stored procedure was last modified in SQL Server 2005. 

A similar technique can be used to determine the last time a user table was altered. The following query illustrates this technique.

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.tables

The modify_date column is updated whenever a column is added or altered for the table. It's also updated if the clustered index is changed.

The query above only works for tables, but the same approach is available for views using the sys.views catalog view.

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.views

Of course, in both of these queries you can add a WHERE clause to limit the results to only those changes that have occurred in the past week, month, year, whatever. Something like:

SELECT
        [name]
       ,create_date
       ,modify_date
FROM
        sys.views
WHERE
        modify_date > DATEADD(day,-30,GETDATE())

Although this is no substitute for true database auditing that captures who modified the table, what was changed, and what the table looked like prior to the change, it does allow me to tell that something did, in fact, change. And as a consultant who regularly works with developers and DBAs at my client's site, I find this information very useful.

To get the former, you'll need to invest some time in DDL triggers or some money into a third party tool.

Remember, these catalog views were first introduced in SQL Server 2005, so these queries will not work in SQL Server 2000.

Cheers!

Joe