Database information - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Database information

Author

Topic

barrievans
Starting Member

2 Posts

Posted - 2010-09-13 : 08:19:42

I have SQL 2000 and need to know when the tables in a database were last updated

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 09:09:28

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.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 09:11:26

The following query will give you the time at which the data in the table was updated.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'your database name')
AND OBJECT_ID=OBJECT_ID('your table name')

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-13 : 10:47:18

The modify_date is just for schema changes, not data changes.
The index_usage_stats will only work for SQL 2005+ (and does not persist over a server restart)

The only reliable way to track this info is to have an insert/update/delete trigger on the table and keep a record of changes/last change.

--
Gail Shaw
SQL Server MVP

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-13 : 10:50:29

quote:
Originally posted by GilaMonster

The modify_date is just for schema changes, not data changes.
The index_usage_stats will only work for SQL 2005+ (and does not persist over a server restart)

The only reliable way to track this info is to have an insert/update/delete trigger on the table and keep a record of changes/last change.

--
Gail Shaw
SQL Server MVP

Sorry I forgot to mentione those points! Thanks Gail.

barrievans
Starting Member

2 Posts

Posted - 2010-09-14 : 03:09:35

Many thanks - I have SQL2000 so I will have to build in own checks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources