I am running a dynamic sql command with sp_msforeachdb for each database. However the command bombs for a certain database.
How is '?' used to display the database name when the error happens? I tried using it in a Catch statement but my syntax is wrong.
Just use DB_NAME()
EXEC sp_msforeachdb 'USE ? SELECT DB_NAME() ...do stuff'
It worked to me:
exec sp_MSforeachdb 'select *, print ''?'' from TABLE'
Depending on the script, you get for all db's the ouput "master" with DB_NAME().
You can use DB_NAME(DB_ID(''?'')) in these cases:
exec sp_msforeachdb 'select DB_NAME(DB_ID(''?'')), Value from ?.dbo.MyTable WHERE(ColumnX = N''1'')'
which would result in something like this:
+-------------+-------+
| DBLegacy | VA1 |
+-------------+-------+
+-------------+-------+
| DBNew12 | ABC |
+-------------+-------+
+-------------+-------+
| DBOld333 | XYZ |
+-------------+-------+
To get the results into one result set see:
SQL Server: sp_MSforeachdb into single result set
This would result in this:
+-------------+-------+
| DBLegacy | VA1 |
+-------------+-------+
| DBNew12 | ABC |
+-------------+-------+
| DBOld333 | XYZ |
+-------------+-------+
