using sp_MSForEachDB in a data flow task

• alen teplitsky

  SSC-Dedicated

  Points: 30014

  More actions

  September 16, 2010 at 1:20 pm

  #146965

  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'

• krabe

  SSC Enthusiast

  Points: 112

  More actions

  February 27, 2015 at 9:35 am

  #1780663

  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'."