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!