>>>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
>

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