Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Last Modified Dates on Database & Tables? Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 6:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, 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
Post #1394579
Posted Monday, December 10, 2012 6:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1394585
Posted Monday, December 10, 2012 7:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, Visits: 239
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.
Post #1394601
Posted Monday, December 10, 2012 7:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:50 PM
Points: 1,669, Visits: 2,207
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)

Internet ATM Machine
Post #1394609
Posted Monday, December 10, 2012 7:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, Visits: 239
Indeed, plenty of food for thought there.

Thanks once again for all your kind help.
Post #1394611
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse