SQL Server sp_msforeachdb breaks

Hi Friends,

I had known this before that SQL Server sp_msforeachdb breaks but what I didn’t know that it is not a common knowledge so I thought I should blog about it.

SQL Server sp_msforeachdb is widely used for SQL Server database maintenance purposes. It is undocumented and unsupported.

Let us take an example.

Suppose, I have this code that produces ALTER statement for all the databases where AUTO_CREATE_STATISTICS is FALSE. The intention is to set it TRUE for all the databases.

SQL Server ms_foreachdb breaks

When this is executed, it runs fine.

Now, I create a database as follows:

SQL Server ms_foreachdb breaks

   

Instantly, what might surprise you that why am I using single quotes (‘) in the database name. Well, I had the same question to my customer and no one had an answer 🙂 – probably a very old database that was created using some script that inserted quotes into it. Well, let that be. To test our scenario, I have set AUTO_CREATE_STATISTICS to FALSE.

SQL Server ms_foreachdb breaks

Now this database being there with AUTO_CREATE_STATISTICS to FALSE, will the above script still run?

No. Here is the error:

SQL Server ms_foreachdb breaks

Well, be careful with this system stored procedure. Avoid using it. In case you use it, you need to make sure it runs correctly and/or does not skip databases.

Want to watch this in action? Watch here!

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “SQL Server sp_msforeachdb breaks”

  1. Sp_msforeachdb did not break – it is working as expected. The T-SQL being submitted to sp_msforeachdb would also fail, outside of sp_msforeachdb. Simply replace the ? parameter with the database name, and try to run string – it will fail with the same syntax error. The problem is that the SQL string omitted the use of QUOTENAME. That omission s a human problem, not an sp_msforeachdb problem.

    The script’s failure to use QUOTENAME also exposes the script to an injection attack…

  2. I totally agree Bill; if your sql syntax is problematic on it’s on, it will be problematic when part of any Stored Procedure execution. Also, used the undocumented sp_msforeachdb proc for over 10 with no issues.

Leave a Reply

Your email address will not be published.