There are times after I notice myself wanting to run a SQL command against every database on one among my SQL Server instances. there’s a handy undocumented stored procedure that permits you to try to do this while not having to set up a cursor against your sysdatabases table within the master database: sp_MSforeachdb.

SP_MSFOREACHDB: sp_MSforeachdb 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:

EXEC sp_MSforeachdb @command  //Where @command is a variable-length string.

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 On A SQL Instance

Example 2: Execute A DDL Query Against All User Databases On A SQL Instance

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 be came 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? 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 On A SQL Instance

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:


Leave a Comment