sp_msforeach db - Why do we need to use the USE keyword

sp_msforeachdb is an undocumented sp which is designed to run some T-SQL against every database in the server instance. Why then, does it appear that I need to use the USE keyword to do that

EXEC sp_MSForEachDb @command1 = 'SELECT DB_NAME()'

Prints the database name the sp_MSForEachDb command was run against n times, where n is the number of databases on the instance.

EXEC sp_MSForEachDb @command1 = 'USE ?; SELECT DB_NAME()'

Prints the name of each database.

Why is it necessary to use the USE statement? Shouldn't this behavior be inherent in the procedure?

The procedure does not perform a USE command for you. The way the procedure works is that it replaces every ? in your command with the database prefix.

If you run this:

USE foodb;
GO
EXEC sys.sp_MSforeachdb N'SELECT * FROM sys.objects;'; 
-- 3,380 total rows on my system

You will also get a number of resultsets that all show the objects from foodb. You have to issue the command this way in order to get the command to execute in the context of each individual database:

EXEC sys.sp_MSforeachdb N'SELECT * FROM ?.sys.objects;'; 
-- 50,603 total rows on my system

In this case it will execute your command for each database, with the ? replaced by the database name:

SELECT * FROM master.sys.objects;
SELECT * FROM tempdb.sys.objects;
...

To call a system function that doesn't support a database prefix typically requires a USE command first. A way to do this differently could be:

EXEC sys.sp_MSforeachdb N'SELECT DB_NAME(DB_ID(''?''));';

Or, more simply:

EXEC sys.sp_MSforeachdb N'SELECT N''?'';';

One reason it works this way is that you might be executing from the context of the current database because it has a static object you want to use in relation to all of the databases. So, imagine you are in foodb and you create this table:

CREATE TABLE dbo.ObjectNameBlacklist
(
  name sysname
);

INSERT dbo.ObjectNameBlacklist(name) VALUES('badword');

You want to identify all the objects in any database that match the names in this table. So you can say:

EXEC sys.sp_MSforeachdb N'SELECT ''?'', name 
  FROM dbo.ObjectNameBlacklist AS onb
  WHERE EXISTS
  (
    SELECT 1 FROM ?.sys.objects WHERE name = onb.name
  );';

You wouldn't want the command to look for dbo.ObjectNameBlacklist in each database. Of course you could prefix that one manually, but you don't have to because of the way the procedure works.

StackExchange Database Administrators Q#216999, answer score: 13