Selecting from a table in ALL your databases in SQL Server

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.

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s