Some databases may show a NULL collation in the sys.databases DMV

While working on my Swart’s Ten Percent Rule post last week, I needed to test the Windows version of the script on my SQL Server 2016 instance. Just before removing all the databases, I noticed something interesting when querying the sys.databases Dynamic Management View (DMV).

Because the process I had come up with involved setting databases offline to allow me to work around space constraints, I noticed that all the databases I created for the test showed a NULL value in the collation_name column.

As this was expected behaviour (the collation cannot be read if the database is offline), I dismissed the vast amount of NULLs in this column. However, once I cleaned up the 32,700-odd extra databases, I discovered there were two databases I was using for another project where the collation_name was also NULL.

When I right-clicked on those databases in SQL Server Management Studio’s Object Explorer to look at the properties, both showed the default collation of SQL_Latin1_General_CP1_CI_AS.

I remembered that Andy Warren had asked a Question of the Day a long time ago on SQLServerCentral.com asking about the cases when the output of the following query would return NULL:

SELECT DATABASEPROPERTYEX('SomeDB', 'Collation');

If we follow that link, we see that the correct answer had three possible options:

• the database name is spelled incorrectly
• the database is online but recovery is not complete
• the database is offline

None of these answered this problem to my satisfaction, but there was a link in the answer to a MSDN article. With trepidation I clicked the link expecting it to fail, but the folks behind Microsoft Docs have done an amazing job keeping old links alive.

The official documentation for DATABASEPROPERTYEX tells us that the Collation property returns the database collation as expected, but if the database “is not started,” it will return a NULL.

This was my clue. It wasn’t that the two databases were offline or in recovery, but that Auto Close was enabled. As soon as I figured it out I facepalmed, because these databases were restored from SQL Server Express Edition which enables Auto Close by default.

Guess what? The sys.databases documentation confirms that, if I’d just started there in the first place.

Look, not every one of these blog posts is gold, but I thought it was a creative way to find the answer. It’s still not as bad as the time I literally forgot the ALTER keyword.

Share your recent facepalm moments in the comments below.