September 16, 2010 at 1:20 pm
i have servers with 30 or so databases each ranging in size from 500GB to a few MB. wanted to get the index usage stats on the larger ones since half of them are probably unused.
when i try to run this in the data flow task in SSIS i only get data for the first database. will it work with all of them or do i have to create separate tasks and connections for each database?
EXEC sp_MSForEachDB
'USE [?]
IF DB_ID(''?'') > 4
select server_name = @@servername, date_read = getdate(), a.database_id, database_name = db_name(), table_name = OBJECT_NAME(a.[object_id]), a.index_id, c.name as index_name, a.user_seeks, a.user_scans, a.user_lookups, a.user_updates,
a.last_user_seek, a.last_user_scan, a.last_user_lookup, a.last_user_update,
a.system_seeks, a.system_scans, a.system_lookups, a.system_updates,
a.last_system_seek, a.last_system_scan, a.last_system_lookup, a.last_system_update
from sys.dm_db_index_usage_stats a
inner join sys.indexes c on c.object_id = a.object_id and a.index_id = c.index_id
where
-- a.database_id in (24)
OBJECTPROPERTY(a.[object_id], ''IsMsShipped'') = 0'
February 27, 2015 at 9:35 am
Did you ever get this solved? I'm trying to write an SSIS package to gather DB/file stats from all of our instances and cannot seem to get the sp_MSForEachDB proceedure to work with SSIS. I get an error stating "Invalid object name '#qtemp'."
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply