Link to home
Start Free TrialLog in
Avatar of edrz01
edrz01Flag for United States of America

asked on

Display SQL 2008 last modified/update Database

I am migrating my 2008 databases to 2012 and only want to migrate Database that are used the most frequent by determining the Last Used / Updated Date. Not the create date or modified date.

My SQL 2008 R2 has 48 database that were created, only a few that haven't been used in years. And others that are being used more frequently. Knowing the Table Dates/Times may be needed in time, and that's where my sql script below will help. But what I'm specifically looking for is the Main Database List.

The following sql script I am able to determine a specific Database (HTEDTA Files) as in the code below:
USE [HTEDTA Files]
GO
SELECT    [TableName] = OBJECT_NAME(object_id),
last_user_update, last_user_seek, last_user_scan, last_user_lookup
FROM    sys.dm_db_index_usage_stats
WHERE    database_id = DB_ID('HTEDTA Files')
Order by TableName
GO
Avatar of Matt Bowler
Matt Bowler
Flag of New Zealand image

I'm not aware of a master list of when databases were last accessed, I think the best approach is something like you have against index_usage_stats - but then loop through all databases and run that. You could look for max(date) or something?
Avatar of edrz01

ASKER

Ok, was hoping to not do that. But I did come up with a shorter script. I suppose I can write the script for each Database. Not sure how I would write a loop to get each individual DB Name.

USE [HTEDTA Files]
Go
SELECT name, modify_date, type_desc
FROM  sys.objects
Where type_desc = 'USER_Table'
ORDER BY modify_date DESC
GO
This is a cheap and cheerful option?

EXEC sp_MSforeachdb 'USE [?] SELECT db_name(), MAX(modify_date) FROM sys.objects Where type_desc = ''USER_Table'''
Other wise you loop through the databases from sys.databases with a while loop or cursor, and build a piece of dynamic sql for each database to do the same thing, then you could pipe the out put into a table or something a bit tidier?
SOLUTION
Avatar of Matt Bowler
Matt Bowler
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of edrz01

ASKER

When I ran your last script, it created a temporary table:
#dates______________________________________________________________________________________________________________000000000154
and an error was passed that it is not able to find #dates, so I am a bit confused.
Avatar of edrz01

ASKER

Disregard the last message. Figured out I needed to include the DB name in the [?] placement. Though it entered 56 records for the one DB [HTEDTA Files]
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No - you should leave the ? in - that's a placeholder for each database. Do you care which object is modified or just how recently any object was modified for each database?
Avatar of edrz01

ASKER

Something burped on my SQL and had to restart. This time I created the table in the MASTER and all the table names and dates have been created Thank You!!!
Avatar of edrz01

ASKER

Thank you very much for your assistance and scripts. It was better in my case to create a Master Table.