How to fix the database does not exist error in sp_MSforeachdb ?
With MS SQL Server, when using the sp_MSforeachdb system stored procedure, the system displays this error : Database does not exist. Make sure that the name is entered correctly. But only in specific cases and for certain databases.
The stored procedure displays the error because the database tries to access a database that does not exist in your SQL Server instance. So it’s obviously not visible in the database list using SSMS. But somehow it appears in the list with the query execution.
SQL Error database does not exist with EXEC sp_MSforeachdb
The full error message is displayed with the use of EXEC sp_msforeachdb, the details are as follows. There is actually no error in the sp_MSforeachdb system stored procedure nor in the sys.sp_MSforeach_worker. This issue is a classical issue with the naming of the database.
The database does not exist. Make sure the name is entered correctly.
Rename the databases without special characters
If the SQL Server database names contain spaces or dashes, you can simply rename them or replace the characters with an underscore. Here is a T-SQL script example to rename a SQL Server database. To be adapted to your specific scenario:
USE [master] GO ALTER DATABASE [database name is-not-good] MODIFY NAME = Database_name_is_good_now; GO
Finally, do not use dashes and spaces in database names. It is technically possible to use them, but to use the system’s stored procedures, it is better to use only underscore characters. Or better still, don’t use any special characters in the names of your database objects.
Using undocumented Microsoft system procedures
Another way to avoid the MS SQL Database does not exist error in sp_MSforeachdb, is to follow the best practice. Note that Microsoft strongly recommends not using these undocumented procedures in a Production environment. Indeed sp_msforeachdb is an undocumented system stored procedure and therefore not officially supported. The alternative solution is to use a loop on the name in a stored procedure.
Finally, here is another article on how to handle the Invalid Object Name error with CTE SQL Server.
