Quick Script that Returns All Properties from SERVERPROPERTY() in SQL Server 2017/2019

The following script returns all properties (and their respective values) from the SERVERPROPERTY() function in SQL Server 2017 and 2019.

The property names are returned in the first column, and their raw values are returned in the second column.

In total, 44 properties are returned by this script.

The Script

SELECT 'BuildClrVersion' AS Property, SERVERPROPERTY('BuildClrVersion') AS Value
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'HadrManagerStatus', SERVERPROPERTY('HadrManagerStatus')
UNION ALL
SELECT 'InstanceDefaultDataPath', SERVERPROPERTY('InstanceDefaultDataPath')
UNION ALL
SELECT 'InstanceDefaultLogPath', SERVERPROPERTY('InstanceDefaultLogPath')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsAdvancedAnalyticsInstalled', SERVERPROPERTY('IsAdvancedAnalyticsInstalled')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsHadrEnabled', SERVERPROPERTY('IsHadrEnabled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsLocalDB', SERVERPROPERTY('IsLocalDB')
UNION ALL
SELECT 'IsPolyBaseInstalled', SERVERPROPERTY('IsPolyBaseInstalled')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'IsXTPSupported', SERVERPROPERTY('IsXTPSupported')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductBuild', SERVERPROPERTY('ProductBuild')
UNION ALL
SELECT 'ProductBuildType', SERVERPROPERTY('ProductBuildType')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ProductMajorVersion', SERVERPROPERTY('ProductMajorVersion')
UNION ALL
SELECT 'ProductMinorVersion', SERVERPROPERTY('ProductMinorVersion')
UNION ALL
SELECT 'ProductUpdateLevel', SERVERPROPERTY('ProductUpdateLevel')
UNION ALL
SELECT 'ProductUpdateReference', SERVERPROPERTY('ProductUpdateReference')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')
UNION ALL
SELECT 'FilestreamShareName', SERVERPROPERTY('FilestreamShareName')
UNION ALL
SELECT 'FilestreamConfiguredLevel', SERVERPROPERTY('FilestreamConfiguredLevel')
UNION ALL
SELECT 'FilestreamEffectiveLevel', SERVERPROPERTY('FilestreamEffectiveLevel');

Sample Result

Here’s the result I get when I run the above script against one of my Mac test servers running SQL Server 2019 on Linux:

+------------------------------+------------------------------+
| Property                     | Value                        |
|------------------------------+------------------------------|
| BuildClrVersion              | v4.0.30319                   |
| Collation                    | SQL_Latin1_General_CP1_CI_AS |
| CollationID                  | 872468488                    |
| ComparisonStyle              | 196609                       |
| ComputerNamePhysicalNetBIOS  | sqlserver007                 |
| Edition                      | Developer Edition (64-bit)   |
| EditionID                    | -2117995310                  |
| EngineEdition                | 3                            |
| HadrManagerStatus            | 1                            |
| InstanceDefaultDataPath      | /var/opt/mssql/data/         |
| InstanceDefaultLogPath       | /var/opt/mssql/data/         |
| InstanceName                 | NULL                         |
| IsAdvancedAnalyticsInstalled | 1                            |
| IsClustered                  | 0                            |
| IsFullTextInstalled          | 0                            |
| IsHadrEnabled                | 0                            |
| IsIntegratedSecurityOnly     | 0                            |
| IsLocalDB                    | 0                            |
| IsPolyBaseInstalled          | 0                            |
| IsSingleUser                 | 0                            |
| IsXTPSupported               | 1                            |
| LCID                         | 1033                         |
| LicenseType                  | DISABLED                     |
| MachineName                  | sqlserver007                 |
| NumLicenses                  | NULL                         |
| ProcessID                    | 28                           |
| ProductBuild                 | 1800                         |
| ProductBuildType             | NULL                         |
| ProductLevel                 | CTP3.2                       |
| ProductMajorVersion          | 15                           |
| ProductMinorVersion          | 0                            |
| ProductUpdateLevel           | NULL                         |
| ProductUpdateReference       | NULL                         |
| ProductVersion               | 15.0.1800.32                 |
| ResourceLastUpdateDateTime   | 2019-07-17 22:15:31          |
| ResourceVersion              | 15.00.1800                   |
| ServerName                   | sqlserver007                 |
| SqlCharSet                   | 1                            |
| SqlCharSetName               | iso_1                        |
| SqlSortOrder                 | 52                           |
| SqlSortOrderName             | nocase_iso                   |
| FilestreamShareName          | MSSQLSERVER                  |
| FilestreamConfiguredLevel    | 0                            |
| FilestreamEffectiveLevel     | 0                            |
+------------------------------+------------------------------+
(44 rows affected)

See the Microsoft documentation for a detailed description of each property.

Also see the slightly improved script that provides explanatory text for some of the rows.