edrz01
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_stat s
WHERE database_id = DB_ID('HTEDTA Files')
Order by TableName
GO
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_stat
WHERE database_id = DB_ID('HTEDTA Files')
Order by TableName
GO
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?
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
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'''
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I ran your last script, it created a temporary table:
#dates____________________ __________ __________ __________ __________ __________ __________ __________ __________ __________ 0000000001 54
and an error was passed that it is not able to find #dates, so I am a bit confused.
#dates____________________
and an error was passed that it is not able to find #dates, so I am a bit confused.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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!!!
ASKER
Thank you very much for your assistance and scripts. It was better in my case to create a Master Table.