CODEWITHSUNDEEP
sql-serversql-server-2008sp-msforeachdbsp-msforeachtable
Michael
Michael

Reputation: 477

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

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

Upvotes: 5

Views: 6071

Answers (2)

Ben Thul
Ben Thul

Reputation: 32697

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

Upvotes: 3

Sean Lange
Sean Lange

Reputation: 33571

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

Upvotes: 0

Related Questions