While attempting to come up with a way to gather index information and store it in my newly created warehouse, I am using the sys.dm_db_index_physical_stats dmv.  At first, I was had plan to call this DMV via linked server such as: [serverA].master.sys.dm_db_index_physical_stats except that SQL won’t let me do that. 

Other DMV’s I’ve used recently allowed me to make calls via linked server.  Live and learn I suppose.  Anyways, msforeachdb is an undocumented procedure that loop through each database and do what you tell it to do, such as changing all the databases from FULL to SIMPLE mode.  It work pretty flawlessly via ad-hoc but the moment you throw this sucker into a stored procedure and execute it using the bcp command, it crap out.  In fact it works fine if you call it within a sql job or through a SSMS query window.  The error that bcp gave me is:

The syntax is pretty much common and found on almost all blogs that discuss SQL:

exec sp_msforeachdb 'select
@@SERVERNAME
, DB_NAME(s.database_id) as name
, OBJECT_NAME(s.object_id) as tableName
, i.name
, s.index_type_desc as indexType
, s.index_depth as indexDepth
, s.page_count as pageCount
, convert(float,round(s.avg_fragmentation_in_percent,2)) as avgFragInPercent
, convert(float,round(s.avg_fragment_size_in_pages,2)) as avgFragSizeInPages
, getdate()
from sys.dm_db_index_physical_stats(db_id(),null, null, null, ''limited'') as s
join sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
where s.index_id = 1
AND s.avg_fragmentation_in_percent >= 50
AND s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0'

At first, I was confused as to why it is attempting to use #qtemp.  Nowhere in my script said to use any temp table.  So I asked SQL to give me the execution plan and opened it up with SQL Sentry.  You can see that msforeachdb make use of cursors and created 10 #qtemp objects.  The note stated /* look for each database */ except that I have 9 databases and not 10.

In the SSMS’ and SQL Sentry execution plan display, it executed 111 times!  It is a pretty mess plan.  So I had to rewrite the query using a while loop and stick it inside the procedure.  Using the while loop executed the query only 28 times and complete much much faster.

As of right now, I haven’t found the reason for the original failed message when attempting to use msforeachdb inside a store procedure.  But I have a feeling that it might have to do the #qtemp table being accessible via the stored procedure and that it is a local temp table not global.