There are times after I notice myself wanting to run a SQL command against every database on one among my SQL Server instances. In this article, I want to show how to do this.

sp_MSforeachdbprocedure is extremely useful for pulling together metadata about your various SQL databases. I use it quite frequently for reporting on such important metrics as database file sizes, amount of free space, and backup status. The syntax for this undocumented procedure is:

You can alternately omit the method of declaring and setting the @command variable. The T-SQL command below behaves identically to the one higher than and is condensed to one line of code:  This query can come back a list of all tables in all databases on a SQL instance: EXEC sp_MSforeachdb ‘USE? select name FROM sysobjects where xtype = ”U” ORDER BY name’

Example 1: Query Information From All Databases

Example 2: Execute A DDL Query Against All User Databases

What about the “?” Placeholder

Throughout the examples provided higher than you may see the use of the question mark as a placeholder for the database/database name. To reference the database name as a string to come back in a query, insert it between a double set of single quotation marks. To treat it as a reference to the database object merely use it by itself (as given in Example 3b.). It’s necessary to line the database for the query to run against, by using the USE?

The statement, otherwise the code can execute within the context of this information, for every information in your SQL instance. If you’ve got 5 databases hosted within the current instance and you were to run the keep procedure code higher than whereas within the context of DBx it’d execute the T-SQL text of the @command five times in DBx. This behavior is clear within the output of Example three below.

Example 3: Query File Information from All Databases

Why Not Just Use a Cursor?

Sure, a cursor can accomplish all that I’ve presented above, but let’s look at the code required to set up a cursor to execute the command used in Example 3:

The undocumented sp_MSforeachdb procedure

The article was published on January 10, 2017 @ 1:59 PM

Leave a Comment