Making Your T-SQL Work Smarter: sp_MSforeachdb

Last time, we showed how to take advantage of a built-in stored to run a command against multiple tables. This time, we look at another for working against a bunch of databases.

In my last post, I showed you how you can take a little built-in stored procedure known as sp_MSforeachtable to run a single command against multiple tables quickly. Now, let's take that approach and apply it across all of the databases on your server using another built-in stored procedure called sp_MSforeachdb.

In this case, let's consider the need to run another popular maintenance task, DBCC CHECKDB. While for many administrators this is as simple as writing the statements for each database on their server, it's not so easy when you have hundreds of databases. Similarly, many consultants have to have maintenance jobs in place that can account for the on-the-fly creation of new databases at their customer sites.

So, let's take a look at getting this done with sp_MSforeachdb. Here's an example:

exec sp_MSforeachdb @command1 = '
    Use ?;

What? Can it be that simple? Yes.

Notice the question mark that functions as a placeholder for the database name. Now, you can add print statements, other T-SQL actions, whatever you need into this command.

One note: You can't combine sp_MSforeachtable and sp_MSforeachdb. Why? Because of the question mark. They both use it as a placeholder; the "outer" proc will always use that placeholder, and try and place the database name (in the case of sp_MSforeachdb) or table name (sp_MSforeachtable) regardless of the call to the other proc.

And one more note: These are still undocumented stored procedures, so Microsoft reserves the right to remove or change these procs as they see fit without warning. Therefore, you have been warned. If you're really geeky (like me), you can script these procs out and use them to write your own versions.

In my next post, we'll talk about generating dynamic SQL that writes code you can either reuse or execute on the fly.

Until then, have fun!

About the Author

Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.


  • Microsoft Talks Teams and SharePoint at Modern Workplace Event

    It's a hybrid world, but remote work is here to stay, according to Microsoft's Teams and SharePoint head Jeff Teper.

  • Malwarebytes Affirms Other APT Attack Methods Used Besides 'Solorigate'

    Security solutions company Malwarebytes affirmed on Monday that alternative methods besides tainted SolarWinds Orion software were used in the recent "Solorigate" advanced persistent threat (APT) attacks.

  • How To Fix the Hyper-V Read Only Disk Problem

    DOS might seem like a relic now, but sometimes it's the only way to fix a problem that Windows seems ill-equipped to deal with -- like this one.

  • Microsoft Warns IT Pros on Windows Netlogon Fix Coming Next Month

    Microsoft on Thursday issued a reminder to organizations to ensure that their systems are properly patched for a "Critical"-rated Windows Netlogon vulnerability before next month's "update Tuesday" patch distribution arrives.

comments powered by Disqus