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 ?;
    DBCC CHECKDB(?, NOINDEX)
'

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.

Featured

  • Hyper-V Architecture: Some Clarifications

    Brien answers two thought-provoking reader questions. First, do Hyper-V VMs have direct hardware access? And second, how is it possible to monitor VM resource consumption from the host operating system?

  • Old Stone Wall Graphic

    Microsoft Addressing 36 Vulnerabilities in December Security Patch Release

    Microsoft on Tuesday delivered its December bundle of security patches, which affect Windows, Internet Explorer, Office, Skype for Business, SQL Server and Visual Studio.

  • Microsoft Nudging Out Classic SharePoint Blogs

    So-called "classic" blogs used by SharePoint Online subscribers are on their way toward "retirement," according to Dec. 4 Microsoft Message Center post.

  • Datacenters in Space: OrbitsEdge Partners with HPE

    A Florida-based startup is partnering with Hewlett Packard Enterprise in a deal that gives new meaning to the "edge" in edge computing.

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.