By: Tim Ford | Updated: 2022-02-24 | Comments (36) | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Scripts
Problem
There are times when I find myself needing to run a SQL command against each database on one of my SQL Server instances. There is a handy undocumented stored procedure that allows you to do this without needing to set up a cursor against your sysdatabases table in the master database. This can be done by using sp_MSforeachdb to run the same command in all databases.
Solution
The sp_MSforeachdb procedure is an undocumented procedure that allows you to run the same command against all databases. There are several ways to get creative with using this command and we will cover these in the examples below. This can be used to select data, update data and even create database objects.
General sp_MSforeachdb Syntax
This is the general syntax, where @command is a variable-length string that contains the query you want to run.
EXEC sp_MSforeachdb @command
The "?" Placeholder
In addition to using a straight command, we will see in the examples below how to use ? placeholder which substitutes the database name which allows us to change the context of which database the command is running in.
Example 1: Query Information From All Databases On A SQL Instance
Here is a simple example of where we query a system table from all databases including the system databases.
--Example 1 --This query will return a listing of all tables in all databases on a SQL instance: DECLARE @command varchar(1000) SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name' EXEC sp_MSforeachdb @command
You can alternately omit the process of declaring and setting the @command variable. The T-SQL command below behaves identically to the one above and is condensed to a single line of code:
--This query will return a listing 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 2: Execute A DDL Query Against All User Databases On A SQL Instance
In this example we will create stored procedure spNewProcedure1 in all databases except for the databases we exclude in the IF statement.
--Example 2
--This statement creates a stored procedure in each user database that will return a listing of all users in a database, sorted by their modification date
DECLARE @command varchar(1000)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?
EXEC(''CREATE PROCEDURE spNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers ORDER BY updatedate DESC'') END'
EXEC sp_MSforeachdb @command
As you may notice, there are additional items to take into consideration when limiting the scope of the sp_MSforeachdb stored procedure, particularly when creating or modifying objects. You must also set the code to execute if the IF statement is true by using the T-SQL keywords BEGIN and END.
You should take note that the USE ? statement is contained within the BEGIN...END block. It is important to remember key T-SQL rules and account for them. In this case the rule that when creating a procedure, the CREATE PROCEDURE phrase must be the first line of code to be executed. To accomplish this you can encapsulate the CREATE PROCEDURE code within an explicit EXEC() function.
Example 3: Query File Information From All Databases On A SQL Instance
Throughout the examples provided above you saw the use of the question mark as a placeholder for the database name. To reference the database name as a string to be returned in a query, it needs to be embed between a double set of single quotation marks. To treat it as a reference to the database object simply use it by itself.
It is necessary to set the database for the query to run against, by using the USE ? statement, otherwise the code will execute in the context of the current database, for each database in your SQL instance. If you have 5 databases hosted in the current instance and you were to run the stored procedure code above while in the context of DBx it would execute the T-SQL text of the @command 5 times in DBx.
So in example 3 we get the correct output since we are using USE ? and then we use ''?'' to return the actual database name in the query.
--Example 3 --This query will return a listing of all files in all databases on a SQL instance: EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
What happens though if we omit the USE ? clause, which sets the scope of the query? As you can see below, though it is apparent the code executed for each database, it never changed context. Pay particular interest to the filename column and you will see that the query executed from within the context of the master database (where I ran the query from) for each database in the SQL instance (as noted by the database name being returned via the use of the "?" placeholder).
--Example 3b --Remove the USE ? clause and you end up executing the query repetitively within the context of the current database: EXEC sp_MSforeachdb 'SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
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:
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = 'USE ' + @DB_Name + '; SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM sys.sysfiles SF'
EXEC sp_executesql @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
Considering the behavior is similar I'd rather type and execute a single line of T-SQL code versus sixteen.
Next Steps
- 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.
- See how this can be used to help you manage SQL Server.
- Check out this related article Making a more reliable and flexible sp_MSforeachdb
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-02-24
