>>Hey Naomi,
>>
>>Is it posible, using the SP_MSForEachTable, to implement a distributed query (i.e. to select data from tables in DB1 while connected to DB2)? I have tried the syntax I thought would work (for a fully qualified table name substitution), but it did not work.
>>
>>>Rollin,
>>>
>>>Please check http://www.mssqlcity.com/Articles/KnowHow/RowCount.htm
>>>
>>>or
>>>
>>>http://www.databasejournal.com/features/mssql/article.php/3441031
>>>
>>>Look also to SP_MSForEachTable
>>>
>
>What exactly you're trying?

I tried the following, but I think the the sp still looks at the tables from the current DB. I can insert the results from the original version into the temp table, then into another table. I was just trying to find a way to do it in one command.

use MyDesignDB
go 
create table tRowCount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
   'insert into tRowCount select 'MyTestDB.' + ''?'', count(*) from 'MyTestDB.' + ?'
select * from tRowCount
    order by rowcnt, tablename