July 30, 2012 at 1:42 pm
In a sql server 2008 r2 database, I am using a table called 'appclose' as a reference table. This table has no triggers. Can you tell me if there is a way I can tell when the last time the data was modified in the table?
July 30, 2012 at 1:45 pm
nope, sorry.
unless you have some sort of auditing already in place, whether a server side trace, Change Data Capture, Change Tracking, SQL Audit, or some thing like triggers or default values to update a column in the table, there is no way to find out when a row was last updated, or which rows(s) might have been updated last.
SQL does not preserve that information anywhere.
you can *kind of* get the information for when it was last accessed in general as well as updated, at least since the last time the SQL service was started, by looking at one of the dmv's related to indexes.
--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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply