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.
When this is executed, it runs fine.
Now, I create a database as follows:
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.
Now this database being there with AUTO_CREATE_STATISTICS to FALSE, will the above script still run?
No. Here is the error:
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!