The Undocumented Sp_MSforeachdb Procedure

Jump To Right Section Show

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:


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

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


-- 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

-- 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

Example 2: Execute A DDL Query Against All User Databases


-- 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 pNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers ORDER BY updatedate DESC'') END'
EXEC sp_MSforeachdb @command

-- 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 pNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers ORDER BY updatedate DESC'') END'

EXEC sp_MSforeachdb @command

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


-- 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'

-- 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'

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 varchar(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 = '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