Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

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

Print | posted on Thursday, October 11, 2007 7:58 AM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# 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
10/15/2007 4:28 AM | Dinesh
Gravatar

# re: When was a SQL Server table or view last altered?

Thanks for the modification date SQL. Just what I needed! -jeff
4/4/2008 3:11 PM | Jeff
Gravatar

# re: When was a SQL Server table or view last altered?

but I need the same queries from SQL 2000, please help.
11/4/2008 9:27 AM | Amit Mehta
Gravatar

# 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?
11/4/2008 2:46 PM | uman
Gravatar

# 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
1/22/2009 11:09 PM | Ashley
Gravatar

# 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.

1/23/2009 6:23 AM | Joe Webb
Gravatar

# 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

2/12/2009 9:21 AM | Jeff
Gravatar

# re: When was a SQL Server table or view last altered?

Very good. Thanks for sharing, Jeff.
2/12/2009 9:34 AM | Joe Webb
Gravatar

# re: When was a SQL Server table or view last altered?

Thanks! , it helps.
7/30/2010 7:05 AM | satish
Gravatar

# 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.
8/25/2010 8:00 PM | H
Gravatar

# re: When was a SQL Server table or view last altered?

The query above only works for tables, but the same approach is available for views using the sys.views catalog view.
10/15/2010 12:53 AM | snow boots for women
Gravatar

# re: When was a SQL Server table or view last altered?

A similar technique can be used to determine the last time a user table was altered. The following query illustrates this technique.
10/16/2010 3:55 PM | columbia jackets
Gravatar

# re: When was a SQL Server table or view last altered?

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

snow boots | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup
10/19/2010 4:27 AM | fur boots
Gravatar

# re: When was a SQL Server table or view last altered?

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under "format" option choose the output video formats by your needs, such as to mp4 for ipod, iphone, zune, psp,
10/26/2010 1:32 AM | hanly
Gravatar

# re: When was a SQL Server table or view last altered?

just wanted to let you konw i enjoy reading your blog and hope you will continue writing them for a long time to come
8/2/2011 12:39 AM | registryspeeder
Gravatar

# 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

8/18/2011 8:52 AM | Atcheswara Reddy
Gravatar

# 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')
8/18/2011 8:56 AM | Atcheswara Reddy
Gravatar

# 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
7/18/2012 3:00 AM | SUresh
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET