SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Last Modified Dates on Database & Tables?


Last Modified Dates on Database & Tables?

Author
Message
raotor
raotor
Right there with Babe
Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)

Group: General Forum Members
Points: 734 Visits: 239
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
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72690 Visits: 40948
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
raotor
Right there with Babe
Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)

Group: General Forum Members
Points: 734 Visits: 239
Ohhh errr Smile

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
sgmunson
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17485 Visits: 4638
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)
Smile Smile Smile
Health & Nutrition
raotor
raotor
Right there with Babe
Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)

Group: General Forum Members
Points: 734 Visits: 239
Indeed, plenty of food for thought there.

Thanks once again for all your kind help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search