How to use both sp_msforeachtable and sp_msforeachdb in the same query?

Question

Is there any way to reference the table inside a 'sp_MSforeachtable' loop running inside a 'sp_msforeachdb' loop?

For example, in the following query the '?' is always referencing the database:

DECLARE @cmd VARCHAR(8000);

SET @cmd = 'USE ?; EXEC sp_MSforeachtable @command1="select db_name = DB_NAME(), db_foreach = ''?'', tb_foreach = ''?'' "'

EXEC sp_msforeachdb @command1 =@cmd

Resulting in:

 db_name        db_forearch    tb_foreach
 ServerMonitor  master         master

I want to have something like:

 db_name        db_forearch    tb_foreach
 ServerMonitor  master         <TABLE_NAME>

What should I change?

---

Solved. I used my ow cursor, as suggested by Sean. But the @replacechar solution suggested by Ben Thul is exactly what I was looking for.

DECLARE @cmd VARCHAR(8000);

SET @cmd = 'USE ^; EXEC sp_MSforeachtable @command1="select db_name = DB_NAME(), db_foreach = ''^'', tb_foreach = ''?'' "'

EXEC sp_msforeachdb @command1 =@cmd, @replacechar = '^'

Answer 1

Take a look at the parameters for sp_msforeachtable. One of them is @replacechar which, by default, is a question mark (i.e. ?). Feel free to pass in another equally unlikely character to occur in a query (maybe a ^).

Of course, I'd be remiss if I didn't mention that depending on what you're trying to do (and I would argue that anything that you're trying to do over all tables is doable this way), there are easier to read (and write) solutions in powershell:

import-module sqlps -disablenamechecking;
$s = new-object microsoft.sqlserver.management.smo.server '.';
foreach ($db in $s.databases) {
   foreach ($table in $db.Tables) {
      $table | select parent, name; --merely list the table and database
   }
}

Answer 2

For what you are doing you could do something like this. Although this is still using the for each db procedure which can be problematic. You will want to add a where clause to the final select statement to filter out some databases (model, tempdb, master, etc)

declare @TableNames table
(
    DatabaseName sysname
    , TableName sysname
)
    insert @TableNames
    EXEC sp_msforeachdb @command1 = 'use ?;select ''?'', name from sys.tables'

select *, 'exec ' + Databasename + '..sp_spaceused [''' + TableName + ']'';' 
from @TableNames