In SQL Server, the SERVERPROPERTY() function returns information about the server instance.

You can use it to return information such as the edition of SQL Server being used, the server name, the machine name, and more. As of SQL Server 2017 and 2019, you can use it to return up to 44 properties.

Syntax

The syntax goes like this:

SERVERPROPERTY ( 'propertyname' )

So it’s very simple. All you need to do is provide the name of the property that you want returned.

Example 1 – Get the Edition

Here’s an example of getting the edition of the current SQL Server instance on one of my test servers.

SELECT SERVERPROPERTY('Edition') AS Edition;

Result:

+----------------------------+
| Edition                    |
|----------------------------|
| Developer Edition (64-bit) |
+----------------------------+

Example 2 – Get Multiple Properties

Here’s an example of returning multiple properties.

SELECT 
  SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion,
  SERVERPROPERTY('ProductLevel') AS ProductLevel,
  SERVERPROPERTY('ProductVersion') AS ProductVersion;

Result:

+-------------------+----------------+------------------+
| BuildClrVersion   | ProductLevel   | ProductVersion   |
|-------------------+----------------+------------------|
| v4.0.30319        | CTP3.2         | 15.0.1800.32     |
+-------------------+----------------+------------------+

Example 3 – Vertical Results

If you need to return more than a few properties, you might want to return each property on a different row, instead of a different column. This will save you from having to scroll horizontally.

Example:

SELECT 'BuildClrVersion' AS Property, SERVERPROPERTY('BuildClrVersion') AS Value
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion');

Result:

+-----------------+--------------+
| Property        | Value        |
|-----------------+--------------|
| BuildClrVersion | v4.0.30319   |
| ProductLevel    | CTP3.2       |
| ProductVersion  | 15.0.1800.32 |
+-----------------+--------------+

If you need to return all properties, check out this Quick Script that Returns All Properties from SERVERPROPERTY() in SQL Server 2017/2019.

Also see the Microsoft documentation for a full list of arguments accepted by this function.