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

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.

If you look at the sp_helptext entry for sp_MSforeachdb, or its weird friend sp_MSforeachtable

EXEC sys.sp_helptext @objname = N'sp_MSforeachdb';
EXEC sys.sp_helptext @objname = N'sp_MSforeachtable';

You'll see they're both just wrappers for sp_MSforeach_worker:

EXEC sys.sp_helptext @objname = N'sp_MSforeach_worker';

All they do is build up a valid <list of things>, but they don't actually loop over them in a meaningful way.

At any rate, Aaron Bertrand's sp_foreachdb is a much better piece of code that doesn't skip databases, etc.

If you run sp_helptext for the procedure, you will see that it creates a cursor with databases' names and then runs sp_MSforeach_worker. Comment from sp_MSforeach_worker:

This is the worker proc for all of the "for each" type procs. Its function is to read the next replacement name from the cursor (which returns only a single name), plug it into the replacement locations for the commands, and execute them. It assumes the cursor "hCForEach***" has already been opened by its caller. worker_type is a parameter that indicates whether we call this for a database (1) or for a table (0)

So, sp_msforeachdb doesn't run a query on each database but runs a query with replaced '?' with database name for each database from master.dbo.sysdatabases.