How can I use sp_msforeachtable and sp_msforeachdb in the same request?
Is there a way to reference a table inside an sp_MSforeachtable loop running inside an sp_msforeachdb loop?
For example, in the following query '?' always refers to 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
As a result:
db_name db_forearch tb_foreach
ServerMonitor master master
I want something like:
db_name db_forearch tb_foreach
ServerMonitor master <TABLE_NAME>
What should I change?
Solved. I used my cursor as Sean suggested. But @replacechar's solution as suggested by Ben Toole 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 = '^'
source to share
Take a look at the parameters for sp_msforeachtable. One is @replacechar, which is a question mark (i.e.?) By default. Feel free to pass another equally unlikely character in the request (maybe a ^).
Of course I would be overlooked if I didn't mention this depending on what you are trying to do (and I would say that whatever you are trying to do across all tables is doable this way) 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
}
}
source to share
For what you do, you can do something like this. Although this is still used for every db procedure that can be problematic. You need 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
source to share