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

Are you just trying to get every table name from each database? — Sean Lange, Dec 02 '14 at 14:40
Nope. My goal is a bit more complex (I want to run a script based on sp_spaceused for every table in the database). The example is just to make it easier to understand. — Michael, Dec 02 '14 at 15:47
Why not just roll your own cursor instead of for each database? There are some issues with that procedure anyway. It doesn't always find every database. https://sqlblog.org/2018/10/22/sp-ineachdb-2 — Sean Lange, Dec 02 '14 at 15:56
I thought using these undocumented stored procedures would be easier to understand it later. Maybe I was wrong. I will write it with my own cursor. Thanks. — Michael, Dec 02 '14 at 16:13

score 3 · Accepted Answer · answered Dec 02 '14 at 16:56

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

score 0 · Answer 2 · answered Dec 02 '14 at 16:26

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

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

2 Answers2