Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

When was a SQL Server table or view last altered?

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

kick it on DotNetKicks.com

Legacy Comments


Dinesh
2007-10-15
re: When was a SQL Server table or view last altered?
Thanks for the info. However, even if Re-indexing is performed, this query would return values.And it is not possible to find out what modification happened in the table - is it just Clust index change or col addition/deletion

Jeff
2008-04-04
re: When was a SQL Server table or view last altered?
Thanks for the modification date SQL. Just what I needed! -jeff

Amit Mehta
2008-11-04
re: When was a SQL Server table or view last altered?
but I need the same queries from SQL 2000, please help.

uman
2008-11-04
re: When was a SQL Server table or view last altered?
I've modified a table through an Object served on ASP, and when I run the query you suggest, the update is not showing, even through I can see the update on the actual table in SQL 2005. Any suggestions?

Ashley
2009-01-22
re: When was a SQL Server table or view last altered?
Nice piece of code, however can you tell me the code to indicate the last time the table had data either selected, updated, inserted, deleted from it without the need for triggers

Joe Webb
2009-01-23
re: When was a SQL Server table or view last altered?
Without using triggers, I don't know of a way to keep track of the last time a table was accessed. I know that's been a request for a while now, but I don't think it's possible at the moment.


Jeff
2009-02-12
re: When was a SQL Server table or view last altered?
Ashley, I found this article on how to find when a table was last queried or updated while searching for this one. Maybe it will help you.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/06/03/determining-the-last-update-to-or-select-against-a-table-without-a-trigger.aspx


Joe Webb
2009-02-12
re: When was a SQL Server table or view last altered?
Very good. Thanks for sharing, Jeff.

satish
2010-07-30
re: When was a SQL Server table or view last altered?
Thanks! , it helps.

H
2010-08-25
re: When was a SQL Server table or view last altered?
Its a nice article. But I am more interested in finding what was changed in a table, and what the table looked like prior to the change. You mentioned it could be done using DDL triggers. Could you point me to article on that or give some code? I will appreciate that. Thanks.

Atcheswara Reddy
2011-08-18
re: When was a SQL Server table or view last altered?
Hi as per my knowledge i found some thing..it might be use full..to find the table last update..

First find the object id
select * from sys.sysobjects

using below query we can find the last update date in a table....find the cloumn name....last_user_update

select * from sys.dm_db_index_usage_stats where object_id='Object_Id'


Thanks
Atcheswara Reddy


Atcheswara Reddy
2011-08-18
re: When was a SQL Server table or view last altered?
Hi Every one,

find this Query for when table update last date



SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DB_name')
AND OBJECT_ID=OBJECT_ID('Table_name')

SUresh
2012-07-18
re: When was a SQL Server table or view last altered?
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DB_name')
AND OBJECT_ID=OBJECT_ID('Table_name')

the above query will not work if the server has been restarted.

SELECT name, create_date, modify_date FROM DB_Name.sys.tables
select * from DB_Name.sys.views