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.
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!
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…
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.