How exactly does sp_msforeachdb work behind the scenes?

I need to troubleshoot an issue I am having, and I need some help understanding how sp_msforeachdb works in order to overcome my issue.

What happens is every time I run sp_msforeachdb, I get an error Msg 102, Level 15, State 1, Incorrect syntax near '61'

An example of my code is as follows:

EXEC SP_msforeachdb 'SELECT ''?'' AS Database
                     FROM ?.sys.objects
                     WHERE name like ''%aetna%''

However, it does not matter what query I have as a parameter to sp_msforeachdb. Every time I get the same error. I do have a database that starts with '61s1d', so that makes me think it has an issue with the DB name, but I honestly do not know what is going behind the scenes on sp_msforeachdb.

Things to note.

• It is the only database that starts with a number

• I can try to use code like "If database is like '%61%' don't do ......", but still the same error.

• I cannot test changing the database name--too many things connected to it.

• If I create a test db that starts with '51', then I also get the error for that database

How can I overcome this?

First of all don't use sp_msforeachdb it has several known issues. You are better off using Aaron Bertrand's version here and here.

However it uses a cursor, dynamic SQL and a replace. You can actually look at the code by using sp_helptext.

EXEC sp_helptext sp_msforeachdb

If you use this code it will fix some of your issues.

EXEC SP_msforeachdb 'SELECT ''?'' AS Database
                 FROM [?].sys.objects
                 WHERE name like ''%aetna%''

The brackets will the specific problem you mentioned. You will however run into issues if you have a database with [ or ] in it.

StackExchange Database Administrators Q#149459, answer score: 17