Last Modified Dates on Database & Tables?

• raotor

  Hall of Fame

  Points: 3346

  More actions

  December 10, 2012 at 6:34 am

  #267296

  Hello,

  I'm sure this is an easy one for all you knowledgeable people out there.

  How can I find the modification date of a database or table?

  BTW, by modification date, I mean the last time it was updated or changed - not just when the table structure was modified as seems to be the case in sys.tables.

  Thanks.

  Steve

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 10, 2012 at 6:46 am

  #1567009

  raotor (12/10/2012)

  ---

  Hello,

  I'm sure this is an easy one for all you knowledgeable people out there.

  How can I find the modification date of a database or table?

  BTW, by modification date, I mean the last time it was updated or changed - not just when the table structure was modified as seems to be the case in sys.tables.

  Thanks.

  Steve

  you can infer the last time (since the server was restarted) the last time a table was selected or updated by looking at the indexes

  you cannot find out the information on a per row basis.

  if you need that level of auditing, you can use some of the many functionalities in SQL Server: some examples(all of which must be set up prior to data being changed):

  SQL Audit, CDC, C2 Level Auditng, Add your own trace, exrtended events, DML triggers on specific tables,

  for last touched index info on a table that i mentioned , here's a code example:

  --based on the ideas from

  --http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

  ;WITH ServerStarted AS

  (

  SELECT

  MIN(last_user_seek) AS first_seek,

  MIN(last_user_scan) AS first_scan,

  MIN(last_user_lookup) AS first_lookup

  FROM sys.dm_db_index_usage_stats

  ),

  ServerFirst AS

  (

  SELECT

  CASE

  WHEN first_seek < first_scan AND first_seek < first_lookup

  THEN first_seek

  WHEN first_scan < first_seek AND first_scan < first_lookup

  THEN first_scan

  ELSE first_lookup

  END AS usage_start_date

  FROM ServerStarted

  ),

  myCTE AS

  (

  SELECT

  DB_NAME(database_id) AS TheDatabase,

  OBJECT_NAME(object_id,database_id) As TheTableName,

  last_user_seek,

  last_user_scan,

  last_user_lookup,

  last_user_update

  FROM sys.dm_db_index_usage_stats

  )

  SELECT

  MIN(ServerFirst.usage_start_date) AS usage_start_date,

  x.TheDatabase,

  x.TheTableName,

  MAX(x.last_read) AS last_read,

  MAX(x.last_write) AS last_write

  FROM

  (

  SELECT TheDatabase,TheTableName,last_user_seek AS last_read, NULL AS last_write FROM myCTE

  UNION ALL

  SELECT TheDatabase,TheTableName,last_user_scan, NULL FROM myCTE

  UNION ALL

  SELECT TheDatabase,TheTableName,last_user_lookup, NULL FROM myCTE

  UNION ALL

  SELECT TheDatabase,TheTableName,NULL, last_user_update FROM myCTE

  ) AS x

  CROSS JOIN ServerFirst

  GROUP BY TheDatabase,TheTableName

  ORDER BY TheDatabase,TheTableName

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• raotor

  Hall of Fame

  Points: 3346

  More actions

  December 10, 2012 at 7:05 am

  #1567025

  Ohhh errr 🙂

  Thanks for that. I'd hoped that SQL server had some simple DMV that would provide this information, but no matter.

  So, no indexes, no way of finding the time a table was last updated.

  Thanks for the code samples.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  December 10, 2012 at 7:22 am

  #1567031

  There are some other ways to get to record level, such as adding a field with a default value of GETDATE(), and/or adding an AFTER INSERT, or AFTER UPDATE trigger (or both), but such things can carry some level of performance hit if the insert or update volume is high enough, so they're not always the right way to go. Once again, the answer is: "it depends". It's usually a question of performance vs. the need for the information.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• raotor

  Hall of Fame

  Points: 3346

  More actions

  December 10, 2012 at 7:26 am

  #1567032

  Indeed, plenty of food for thought there.

  Thanks once again for all your kind help.