How to Get SQL Server Info Using SERVERPROPERTY?

Leave a Comment / Microsoft SQL Server
Get SQL Server Info Using SERVERPROPERTY

In this article, we will explain how to Get SQL Server info using SERVERPROPERTY, we will also provide a complete SQL Query to get all detailed information of the current installed SQL Server, like version, instance name, build number …etc.

Table of Contents
  1. 1 What’s the SERVERPROPERTY in SQL Server?
    1. 1.1 SERVERPROPERTY Syntax
    2. 1.2 SERVERPROPERTY Values
  2. 2 Get SQL Server Info Using SERVERPROPERTY()
    1. 2.1 How to get the Machine Name in SQL Server?
      1. 2.1.1 Example
    2. 2.2 How to Get SQL Server Version using @@Version?
      1. 2.2.1 Example
    3. 2.3 How to get Computer Name Physical Net BIOS in SQL Server?
      1. 2.3.1 Example
    4. 2.4 How to get full SQL Server Instance Name?
      1. 2.4.1 Example
    5. 2.5 How to get SQL Server Instance Name?
      1. 2.5.1 Example
    6. 2.6 How to get default data files path in SQL Server?
      1. 2.6.1 Example
      2. 2.6.2 How to get default log files path in SQL Server?
      3. 2.6.3 Example
    7. 2.7 How to get SQL Server Edition?
      1. 2.7.1 Example
    8. 2.8 How to get SQL Server Edition ID and its corresponding name?
      1. 2.8.1 Example
      2. 2.8.2 How to get SQL Server Engine Edition?
      3. 2.8.3 Example
    9. 2.9 How to get SQL Server Product Build?
      1. 2.9.1 Example
    10. 2.10 How to get SQL Server Product Build Type?
      1. 2.10.1 Example
    11. 2.11 How to get SQL Server Product Version Level?
      1. 2.11.1 Example
    12. 2.12 How to get SQL Server Build Number?
      1. 2.12.1 Example
    13. 2.13 How to get SQL Server Product Version Minor Level?
      1. 2.13.1 Example
    14. 2.14 How to get the current Cumulative update installed in SQL Server?
      1. 2.14.1 Example
    15. 2.15 How to get the version of the Microsoft .NET Framework in SQL Server?
      1. 2.15.1 Example
    16. 2.16 How to get Collation Name in SQL Server?
      1. 2.16.1 Example
    17. 2.17 How to get locale identifier in SQL Server?
      1. 2.17.1 Example
    18. 2.18 How to if the Server is in single-user mode in SQL Server?
      1. 2.18.1 Example
    19. 2.19 How to check if the security mode in SQL Server?
      1. 2.19.1 Example
    20. 2.20 How to check Always On availability Status in SQL Server?
      1. 2.20.1 Example
    21. 2.21 How to check the status of the Always On availability groups manager in SQL Server?
      1. 2.21.1 Example
    22. 2.22 How to check if the Advanced Analytics is installed in SQL Server?
      1. 2.22.1 Example
      2. 2.22.2 How to check if if the failover cluster is configured or not in SQL Server?
      3. 2.22.3 Example
      4. 2.22.4 How to check if the full text installed or not in SQL Server?
      5. 2.22.5 Example
    23. 2.23 How to get Process ID of SQL Server Service?
      1. 2.23.1 Example
    24. 2.24 How to get SQL Server Instance Information Remotely?
      1. 2.24.1 Example
        1. 2.24.1.1 Reference

Download the full SQL Query from GitHub at Get The Detailed SQL Server Information.

What’s the SERVERPROPERTY in SQL Server?

SERVERPROPERTY is a System Defined function used to return the SQL Server Instance Information.

SERVERPROPERTY Syntax

SERVERPROPERTY ('propertyname')

SERVERPROPERTY Values

“propertyname“ is one of the following values.

  1. MachineName.
  2. ComputerNamePhysicalNetBIOS.
  3. ServerName.
  4. InstanceName.
  5. InstanceDefaultDataPath.
  6. InstanceDefaultLogPath.
  7. Edition.
  8. EditionID.
  9. EngineEdition.
  10. ProductBuild.
  11. ProductBuildType.
  12. ProductLevel.
  13. ProductMajorVersion.
  14. ProductMinorVersion.
  15. ProductUpdateLevel.
  16. ProductVersion.
  17. BuildClrVersion.
  18. Collation.
  19. LCID.
  20. IsSingleUser.
  21. IsIntegratedSecurityOnly.
  22. IsHadrEnabled.
  23. HadrManagerStatus.
  24. IsAdvancedAnalyticsInstalled.
  25. IsClustered.
  26. IsFullTextInstalled.
  27. ProcessID.

Get SQL Server Info Using SERVERPROPERTY()

In this section, we will explore the most common used SERVERPROPERTY in SQL Server.

How to get the Machine Name in SQL Server?

MachineName is a Server Property that used to get the computer name on which the SQL server instance is running.

Example

select SERVERPROPERTY('MachineName') as 'MachineName'
SERVERPROPERTY('MachineName')

For the cluster, it returns the virtual server name.

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to Get SQL Server Version using @@Version?

Actually, @@Version is not a server property, but it’s an easy way to get the version name and build number.

Example

select @@version as 'Version'
Getting the current Cumulative Update - Service Pack installed - Get Version3

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

You might also like to read How to Get SQL Server Build Numbers?

How to get Computer Name Physical Net BIOS in SQL Server?

ComputerNamePhysicalNetBIOS is a Server Property that used to get the NetBIOS name of the local computer on which the SQL server instance is running.

Example

select SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as 'ComputerName PhysicalNetBIOS'
SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get full SQL Server Instance Name?

ServerName is a Server Property that used to get the full SQL Server instance name.

Example

select SERVERPROPERTY('ServerName') as 'Server Name'
SERVERPROPERTY('ServerName')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get SQL Server Instance Name?

InstanceName is a Server Property that used to only get the instance name without the domain name.

Example

select SERVERPROPERTY('InstanceName') as 'InstanceName'
SERVERPROPERTY('InstanceName')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get default data files path in SQL Server?

InstanceDefaultDataPath is a Server Property that used to get the default path of data files.

Example

select SERVERPROPERTY('InstanceDefaultDataPath') as 'Data Path'
SERVERPROPERTY('InstanceDefaultDataPath')

Applies To

  • SQL Server 2012 through the current version of updates beginning in late 2015.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get default log files path in SQL Server?

InstanceDefaultLogPath is a Server Property that used to get the default path of log files.

Example

select SERVERPROPERTY('InstanceDefaultLogPath') as 'Log Path'
SERVERPROPERTY('InstanceDefaultLogPath')

Applies To

  • SQL Server 2012 through the current version of updates beginning in late 2015.
  • SQL Server 2014.
  • SQL Server 2016
  • SQL Server 2017.

How to get SQL Server Edition?

Edition is a Server Property that used to get the Installed product edition.

Example

select SERVERPROPERTY('Edition') as 'Edition'
SERVERPROPERTY('Edition')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get SQL Server Edition ID and its corresponding name?

EditionID is a Server Property that used to get the Installed product edition ID.

Example

declare @EditionID as sql_variant
set @EditionID= (select SERVERPROPERTY('EditionID'))
select @EditionID as EditionID,
case @EditionID
when -1534726760 then 'Standard'
when 1804890536 then 'Enterprise'
when 1872460670 then 'Enterprise Edition: Core-based Licensing'
when 610778273 then 'Enterprise Evaluation'
when 284895786 then 'Business Intelligence'
when -2117995310 then 'Developer'
when -1592396055 then 'Express'
when -133711905 then 'Express with Advanced Services'
when 1293598313 then 'Web'
when 1674378470 then 'SQL Database or SQL Data Warehouse'
end as 'Edition Based on ID'
SERVERPROPERTY('EditionID')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get SQL Server Engine Edition?

EngineEdition is a Server Property that used to get the Database Engine edition.

Example

declare @EngineEdition as sql_variant
set @EngineEdition = (select SERVERPROPERTY('EngineEdition'))
select @EngineEdition as EngineEdition,
case @EngineEdition
when 1 then 'Personal or Desktop Engine'
when 2 then 'Standard'
when 3 then 'Enterprise'
when 4 then 'Express'
when 5 then ' SQL Database'
when 6 then 'SQL Data Warehouse'
end as 'Engine Edition Based on ID'
SERVERPROPERTY('EngineEdition')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get SQL Server Product Build?

ProductBuild is a Server Property that used to get the build number.

Example

select SERVERPROPERTY('ProductBuild') as 'ProductBuild'
SERVERPROPERTY('ProductBuild')

Applies To

  • SQL Server 2014 beginning October 2015,
  • SQL Server 2016.
  • SQL Server 2017.

How to get SQL Server Product Build Type?

ProductBuildType is a Server Property that used to get the type of build name.

Example

declare @ProductBuildType as sql_variant
set @ProductBuildType = (select SERVERPROPERTY('ProductBuildType'))
select @ProductBuildType as ProductBuildType,
case @ProductBuildType
when 'OD' then 'On Demand release'
when 'GDR' then 'General Distribution Release'
else 'Not applicable'
end as 'ProductBuild Type'
SERVERPROPERTY('ProductBuildType')

Applies To

  • SQL Server 2012 through the current version of updates beginning in late 2015.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get SQL Server Product Version Level?

ProductLevel is a Server Property that used to get the version level as

  • ‘RTM’ = Original release version
  • ‘SPn‘ = Service pack version
  • ‘CTPn‘, = Community Technology Preview version

Example

select SERVERPROPERTY('ProductMajorVersion') as 'ProductMajor Version'
SERVERPROPERTY('ProductLevel')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get SQL Server Build Number?

ProductVersion is a Server Property that used to get the product version as major.minor.build.revision.

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

Example

select SERVERPROPERTY('ProductVersion') as 'Product Version'
SERVERPROPERTY('ProductVersion')

You might also like to read How to Get SQL Server Build Numbers?

How to get SQL Server Product Version Minor Level?

ProductMinorVersion is a Server Property that used to get the minor version.

Example

select SERVERPROPERTY('ProductMinorVersion') as 'ProductMinor Version'
SERVERPROPERTY('ProductMinorVersion')

Applies To

  • SQL Server 2012 through the current version of updates beginning in late 2015.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get the current Cumulative update installed in SQL Server?

ProductUpdateLevel is a Server Property that used to get the current Cumulative update installed name as CUn.

Example

select SERVERPROPERTY('ProductUpdateLevel') as 'ProductUpdate Level'
SERVERPROPERTY('ProductUpdateLevel')

You might also like to read SQL Server: How to get the current installed update level

Applies To

  • SQL Server 2012 through the current version of updates beginning in late 2015.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get the version of the Microsoft .NET Framework in SQL Server?

BuildClrVersion is a Server Property that used to get the version of the Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server.

Example

select SERVERPROPERTY('BuildClrVersion') as 'BuildClr Version'
SERVERPROPERTY('BuildClrVersion')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get Collation Name in SQL Server?

Collation is a Server Property that used to get the name of the default collation for the server.

Example

select SERVERPROPERTY('Collation') as 'Collation'
How to Get SQL Server Info Using SERVERPROPERTY?

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get locale identifier in SQL Server?

LCID is a Server Property that used to get the locale identifier (LCID) of the collation.

Example

select SERVERPROPERTY('LCID') as 'LCID'
SERVERPROPERTY('LCID')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to if the Server is in single-user mode in SQL Server?

IsSingleUser is a Server Property that used to check if the Server is in single-user mode.

Example

declare @IsSingleUser as sql_variant
set @IsSingleUser = (select SERVERPROPERTY('IsSingleUser'))
select @IsSingleUser as IsSingleUserID,
case @IsSingleUser
when 0 then 'Multiple User'
when 1 then 'Single user'
else 'Invalid Input'
end as 'IsSingleUser'
SERVERPROPERTY('IsSingleUser')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to check if the security mode in SQL Server?

IsIntegratedSecurityOnly is a Server Property that used to check the integrated security mode.

Example

declare @IsIntegratedSecurityOnly as sql_variant
set @IsIntegratedSecurityOnly = (select SERVERPROPERTY('IsIntegratedSecurityOnly'))
select @IsIntegratedSecurityOnly as IsIntegratedSecurityOnly,
case @IsIntegratedSecurityOnly
when 0 then 'Windows and SQL Server Authentication'
when 1 then ' Integrated security (Windows Authentication)'
else 'Invalid Input'
end as 'Integrate dSecurity Type'
SERVERPROPERTY('IsIntegratedSecurityOnly')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to check Always On availability Status in SQL Server?

IsHadrEnabled is a Server Property that used to check Always On availability groups is enabled or disabled.

Applies To

  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

Example

declare @IsHadrEnabled as sql_variant
set @IsHadrEnabled = (select SERVERPROPERTY('IsHadrEnabled'))
select @IsHadrEnabled as IsHadrEnabled,
case @IsHadrEnabled
when 0 then 'The Always On availability groups is disabled'
when 1 then 'The Always On availability groups is enabled'
else 'Invalid Input'
end as 'Hadr'
SERVERPROPERTY('IsHadrEnabled')

How to check the status of the Always On availability groups manager in SQL Server?

HadrManagerStatus is a Server Property that used to check the Always On availability groups manager status.

Example

declare @HadrManagerStatus as sql_variant
set @HadrManagerStatus = (select SERVERPROPERTY('HadrManagerStatus'))
select @HadrManagerStatus as HadrManagerStatus,
case @HadrManagerStatus
when 0 then 'Not started, pending'
when 1 then 'Started and running'
when 2 then 'Not started and failed'
else 'Invalid Input'
end as 'HadrManager Status'
SERVERPROPERTY('HadrManagerStatus')

Applies To

  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to check if the Advanced Analytics is installed in SQL Server?

IsAdvancedAnalyticsInstalled is a Server Property that used to check the Advanced Analytics status.

Example

declare @IsAdvancedAnalyticsInstalled as sql_variant
set @IsAdvancedAnalyticsInstalled = (select SERVERPROPERTY('IsAdvancedAnalyticsInstalled'))
select @IsAdvancedAnalyticsInstalled as IsAdvancedAnalyticsInstalled ,
case @IsAdvancedAnalyticsInstalled
when 0 then 'Advanced Analytics was not installed'
when 1 then 'Advanced Analytics was installed'
else 'Invalid Input'
end as 'AdvancedAnalyticsInstalled Status'
SERVERPROPERTY('IsAdvancedAnalyticsInstalled')

Applies To

  • SQL Server 2016.
  • SQL Server 2017.

How to check if if the failover cluster is configured or not in SQL Server?

IsClustered is a Server Property that used to check if the failover cluster is configured or not.

Example

declare @IsClustered as sql_variant
set @IsClustered = (select SERVERPROPERTY('IsClustered'))
select @IsClustered as IsClustered ,
case @IsClustered
when 0 then 'Not Clustered'
when 1 then 'Clustered'
else 'Invalid Input'
end as 'IsClustered Status'
SERVERPROPERTY('IsClustered')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to check if the full text installed or not in SQL Server?

IsFullTextInstalled is a Server Property that used to check if The full-text and semantic indexing components are installed or not.

Example

declare @IsFullTextInstalled as sql_variant
set @IsFullTextInstalled = (select SERVERPROPERTY('IsFullTextInstalled'))
select @IsFullTextInstalled as IsFullTextInstalled ,
case @IsFullTextInstalled
when 0 then 'Full-text and semantic indexing components are not installed'
when 1 then 'Full-text and semantic indexing components are installed'
else 'Invalid Input'
end as 'IsFullTextInstalled Status'
SERVERPROPERTY('IsFullTextInstalled')

Applies To

  • SQL Server 2008, 2012 ,2014,2016,2017.

How to get Process ID of SQL Server Service?

ProcessID is a Server Property that used to get the Process ID of the SQL Server service.

How to Get SQL Server Info Using SERVERPROPERTY?

Example

select SERVERPROPERTY('ProcessID') as 'ProcessID'
SERVERPROPERTY('ProcessID')

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

How to get SQL Server Instance Information Remotely?

You can use Windows PowerShell to invoke SQL command on a reachable server within the network using Invoke-Sqlcmd cmdlet as the following:

  • Open Windows PowerShell as Administrator
  • Type the Invoke-Sqlcmd with the below parameters.
    • -query: the SQL query that you need to run on the remote server.
    • -ServerInstance: the SQL server instance name.
    • -Username: the username that has sufficient permission to access and execute SQL query on the remote server.
    • -Password: the password of the elevated user.

Example

Invoke-Sqlcmd -query "select SERVERPROPERTY('MachineName') as 'MachineName'" -ServerInstance "epm\epmdb" -Username sa -Password *****
How to Get SQL Server Info Using SERVERPROPERTY?
Reference
Download
Get SQL Server Info Using SERVERPROPERTY()
Get SQL Server Info Using SERVERPROPERTY()

Leave a Comment

Your email address will not be published. Required fields are marked *

Subscribe to Our Newsletter

Aenean massa feugiat imperdiet a scelerisque et morbi tempus massa tincidunt vitae libero aenean tincidunt molestie.

Scroll to Top