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.
- By Joshua Jones
- 03/22/2010
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.