The following query doesn't list all the databases in the server

Heyas,

The following query:

DECLARE @command varchar(1000)
select @command = 'USE ?; select DB_NAME(), default_schema_name
, type_desc
, name
, create_date
from sys.database_principals
order by default_schema_name
, type_desc'
EXEC sp_MSforeachdb @command

Doesn't list all the database on the server I am running it on. I do get a one error message: Msg 911, Level 16, State 1, Line 1
Database 'Chris21PROD' does not exist. Make sure that the name is entered correctly.

How do make the query list all the databases in the server, any assistance is appreciated?

Thank you.
ZackGeneral IT Goto GuyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chaauConnect With a Mentor Commented:
The sp_MSforeachdb is undocumented. It uses a wrong table when builds a cursor. As a result it gives you wrong results.

Read here (there is a big article search for sp_MSforeachdb). Aaron recommends stopping using this SP. He has provided an example of his own version of the sp. Switch to this version instead
0
 
ZackGeneral IT Goto GuyAuthor Commented:
Cheers for the info I will use Aarons SP.
0
All Courses

From novice to tech pro — start learning today.