Need to capture database name when error occurs wi

Question

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.

Answer 1

Just use DB_NAME()

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME() ...do stuff'

Answer 2

It worked to me:

exec sp_MSforeachdb 'select *, print ''?'' from TABLE'

Answer 3

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