I’m not going to beat around the bush. I hate the SERVERPROPERTY() and DATABASEPROPERTYEX() system functions.
Loathing
The thing I hate the most about these two functions is that you need to know the right magic spells to make them work. Let’s look at SERVERPROPERTY() first. The syntax for the function is SERVERPROPERTY( 'propertyname' ), which is easy enough syntax, but the list of values for propertyname isn’t discoverable from SQL Server metadata, DMVs, or even IntelliSense. Instead, I need to check the docs for the list of allowable values. These property names are essentially magic words, and I need to check my spell book to make sure I get it right.
Invalid values for propertyname just return NULL–which is easy enough to handle, but also means your code will compile and run, but might do unintended things if you get your magic spell wrong, due to a typo.
Unadulterated loathing
Even for those of us who have spent decades of using SQL Server full-time, it’s nearly impossible to memorize the whole list. Not to mention new properties get added in new versions–and keeping up with the latest magical spells is hard!
Some of the property names are easy to remember, or even guess. You can query SELECT SERVERPROPERTY('Edition'), and it will return something like ‘Developer Edition’, ‘Express Edition’, or ‘Enterprise Edition: Core-based Licensing’. That makes tons of sense!
What if you need the server’s name? @@SERVERNAME gives you the instance name–which for Failover Cluster Instances & named instances, you’ll get something other than (just) the server/node name. But there’s a server property to give you that! What’s it called? The name is unusually and exceedingly peculiar. One of these is right, but I can never remember which of these returns what I want:
SELECT SERVERPROPERTY('PhysicalComputerNameNetBIOS'),
SERVERPROPERTY('NetBIOSPhysicalComputerName'),
SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
SERVERPROPERTY('NetBIOSComputerNamePhysical');
If you guessed ComputerNamePhysicalNetBIOS, congrats! You’ve managed to memorize the right cryptic keywords!
And if you’re looking for version info, you can use @@VERSION and parse the big block of text, or you can use the discrete server properties…. Assuming you can remember which property is which:
- ProductVersion
- ProductMajorVersion
- ProductMinorVersion
- ProductLevel
- ProductUpdateLevel
- ProductBuild
- ProductBuildType
- ProductUpdateReference
And DATABASEPROPERTYEX() isn’t much better. A lot of the documented properties are available as columns in sys.databases, but not all of them (LastGoodCheckDbTime is only available from the function, not sys.databases).
You can also check to see if a database is updatable with DATABASEPROPERTYEX()! This is a simplified check over checking multiple spots to see if the database is online, an Availability Group primary or secondary, marked as read only, log shipping secondary, etc. Again, I think to myself, “What’s the property called?”
SELECT DbName = db.name,
-- There are a ton of properties named "Is_______"
IsUpdatable = DATABASEPROPERTYEX(db.name, 'IsUpdatable'),
-- Maybe this one is not boolean though, and returns multiple values?
Updatable = DATABASEPROPERTYEX(db.name, 'Updatable'),
-- Oh wait! They made up a word for it. But...how did they spell it?
Updatability = DATABASEPROPERTYEX(db.name, 'Updatability'),
Updateability = DATABASEPROPERTYEX(db.name, 'Updateability')
FROM sys.databases AS db;
If you’re guessing along, the right answer is “Updateability” (with an e). I don’t think this is even a real word. “updatable” is a real word (without an e). Grammar & spelling aside, I never remember this stuff.
For your face. Your voice. Your clothing.
I really wish these properties were just available in a couple Dynamic Management Views (DMVs). It would be SO MUCH EASIER To just query sys.server_properties or sys.database_properties. I was kvetching to Aaron Bertrand (blog|twitter) a few days ago, when he pushed me to the obvious realization that it would be easy enough to add into my free, open-source DBA database.
You can now query dbo.ServerProperties in the latest version of my DBA database to get all of the server properties at once, without having to remember any magical incantations. I’ve even included some help text to help explain the difference in some of the confusing properties, or to explain that you’re seeing NULL for the InstanceDefaultBackupPath property because it is only available in SQL Server 2019 & newer.
There’s also a view for dbo.DatabaseProperties, which is similar. This view shows you every property for every database, and also includes help text.
Let’s just say, I loathe it all!
There’s one more thing that I really hate about SERVERPROPERTY() and DATABASEPROPERTYEX(): they return values as sql_variant. The sql_variant data type is kind of wonky compared to other data types. You end up having to explicitly CONVERT it constantly, especially for comparisons, as implicit conversions play by different rules with sql_variant.
The following rules apply to sql_variant comparisons:
- When sql_variant values of different base data types are compared and the base data types are in different data type families, the value whose data type family is higher in the hierarchy chart is considered the greater of the two values.
- When sql_variant values of different base data types are compared and the base data types are in the same data type family, the value whose base data type is lower in the hierarchy chart is implicitly converted to the other data type and the comparison is then made.
- When sql_variant values of the char, varchar, nchar, or nvarchar data types are compared, their collations are first compared based on the following criteria: LCID, LCID version, comparison flags, and sort ID. Each of these criteria are compared as integer values, and in the order listed. If all of these criteria are equal, then the actual string values are compared according to the collation.
In the theme of “Stuff Andy Hates,” I would say dealing with the conversion & comparison challenges of sql_variant is near the top of the list. Because of that, the returned data type for the property values from both of these views is nvarchar(128). This makes comparison a wee bit easier, though depending on your use case you might still want to do explicit conversions for numeric/non-string properties.
Unadulterated loathing! 🧙♀️🪄✨Boo!
Finale
Take it for a spin. You can install my DBA Database by cloning the repo & running the PowerShell install script. You can also download the views separately here and here.
And if you’ve got any questions/comments/problems/improvements, feel free to submit an issue or pull request to the repo!