How to run same query on multiple databases 1

[angiem]

I am trying to count the records on the same table but on different databases then I want to put it in another table and read the results from there. The code I have at the moment is this

declare @cmd1 varchar(500)
set @cmd1 =
'if ''?'' like ''abcd%'' Select ''?'', count(*)
From tablename
exec sp_MSforeachdb @command1=@cmd1

the problem is that it is producing the same results for all the databases which I know is wrong. Can anyone help or is there a better way.

Angie

http://www.millardconsulting.ca

 

[SQLDenis]

declare @cmd1 varchar(500)
set @cmd1 =
   'if ''?'' like ''abcd%''  Select ''?'', count(*)
From  [?]..tablename' 
exec sp_MSforeachdb @command1=@cmd1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[angiem]

I now have to do it across different servers too and changed the code it now ignores the 'if' statement and gives the error message

OLE DB provider 'servername' does not contain table '"master"."tablename"'

declare @cmd1 varchar(500)
set @cmd1 =
'if ''?'' like ''abcd%'' Select ''?'', count(*)
From [servername].[?]..tablename'
exec sp_MSforeachdb @command1=@cmd1

thanks

Angie