I knew about the system stored procedure sp_MSforeachdb but had never really used it until recently. The books on line weren’t much help so I turned to Google and found this link.
If you fully identify the database you can easily do something like:
select * from mydb1.dbo.tblwhatever
select * from mydb2.dbo.tblwhatever
select * from mydb3.dbo.tblwhatever
But what if you don’t know which of your myriad databases has the table you’re looking for? You can use
Exec sp_MSforeachdb ‘select * from [?].dbo.tblwhatever’;
The ‘[?]’ takes the place of each individual db the stored procedure is parsing through: very much like a cursor. If you don’t use the ‘[?].dbo’ syntax you will simply be issuing the command on your current database. The square brackets can be useful if you have funky database names with abnormal characters (I find it easier to leave them in). Of course, you’re going to run into errors if any of your databases doesn’t contain that table. You could put that statement in an if exists loop or be lazy, like I generally am, and just ignore the errors in Management Studio simply looking at the results. You could filter out the system tables with something fancy like this:
Exec sp_MSforeachdb ‘select so.* From [?]..sysobjects so where xtype= ”U” And name = ”tblwhatever”’;
One problem with this arrangement is that it doesn’t tell you in the results which came from which database. A way around this is to chain two statements together like:
Exec sp_MSforeachdb ‘select ”?” select * from [?].dbo.tblwhatever’;
and then you’ll get two results tables for each database, the first one of which is the name of the database. You do need to be careful with the quotage though: it’s two single quotes then the ? and then another two single quotes to indicate that you really want the single quote instead of the end of the parameter string for sp_MSforeachdb.
Golbing 