How exactly does sp_msforeachdb work behind the scenes?

Question

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?

Answer 1

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.

Answer 2

Not disagreeing with anything that @Kenneth said, I should point out that the error you are encountering has nothing to do with sp_MSForEachDB. It is due to how the Database was named: it starts with a number. The rules for naming objects (not just Databases) are detailed in the MSDN page for Database Identifiers. If you follow the "Rules for Regular Identifiers" then you don't need to enclose those names in square brackets or double-quotes. But names that fall outside of those rules do need to be enclosed (always).