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.


  • Spaceflight Training in the Middle of a Pandemic

    Surprisingly, the worldwide COVID-19 lockdown has hardly slowed down the space training process for Brien. In fact, it has accelerated it.

  • Surface and ARM: Why Microsoft Shouldn't Follow Apple's Lead and Dump Intel

    Microsoft's current Surface flagship, the Surface Pro X, already runs on ARM. But as the ill-fated Surface RT showed, going all-in on ARM never did Microsoft many favors.

  • IT Security Isn't Supposed To Be Easy

    Joey explains why it's worth it to endure a little inconvenience for the long-term benefits of a password manager and multifactor authentication.

  • Microsoft Makes It Easier To Self-Provision PCs via Windows Autopilot When VPNs Are Used

    Microsoft announced this week that the Windows Autopilot service used with Microsoft Intune now supports enrolling devices, even in cases where virtual private networks (VPNs) might get in the way.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.