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

thanks for the help, but there are two things. I get a syntax error, and also, dbo is not the only schema. I did notice in the original version that the schema was pulled as part of the table name automatically.