Introduction

In this article, we will explain 
  • How to get the SQL Server Instance Information using "SERVERPROPERTY"?
  • How to get the SQL Server Instance Information remotely?

What's SERVERPROPERTY?


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

SERVERPROPERTY Syntax


SERVERPROPERTY ( 'propertyname' )

"propertyname" can be one of the following values.

  • MachineName.
  • ComputerNamePhysicalNetBIOS.
  • ServerName.
  • InstanceName.
  • InstanceDefaultDataPath.
  • InstanceDefaultLogPath.
  • Edition.
  • EditionID.
  • EngineEdition.
  • ProductBuild.
  • ProductBuildType.
  • ProductLevel.
  • ProductMajorVersion.
  • ProductMinorVersion.
  • ProductUpdateLevel.
  • ProductVersion.
  • BuildClrVersion.
  • Collation.
  • LCID.
  • IsSingleUser.
  • IsIntegratedSecurityOnly.
  • IsHadrEnabled.
  • HadrManagerStatus.
  • IsAdvancedAnalyticsInstalled.
  • IsClustered.
  • IsFullTextInstalled.
  • ProcessID.

MachineName


Description

Get the computer name on which the SQL server instance is running.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.

Example


select SERVERPROPERTY('MachineName') as 'MachineName'


ComputerNamePhysicalNetBIOS


Description

Get the NetBIOS name of the local computer on which the SQL server instance is running.

Applies To

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

Example


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


ServerName


Description

Get the full SQL Server instance name.

Applies To

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

Example


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




InstanceName


Description

Get the instance name.

Applies To

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

Example


select SERVERPROPERTY('InstanceName') as 'InstanceName'



InstanceDefaultDataPath


Description

Get the default path of data files.

Applies To

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

Example


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



InstanceDefaultLogPath


Description

Get the default path of log files.

Applies To

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

Example


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



Edition


Description

Get the Installed product edition.

Applies To

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

Example


select SERVERPROPERTY('Edition') as 'Edition'



EditionID


Description

Get the Installed product edition ID.

Applies To

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

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'



EngineEdition


Description

Get the Database Engine edition.

Applies To

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

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 then 'Express'
when then ' SQL Database'
when 6 then 'SQL Data Warehouse'
end as 'Engine Edition Based on ID'


ProductBuild


Description

Get the build number.

Applies To

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

Example


select SERVERPROPERTY('ProductBuild') as 'ProductBuild'


ProductBuildType


Description

Get the type of build name.

Applies To

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

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'



ProductLevel


Description

Get the version level as 
  • 'RTM' = Original release version
  • 'SPn' = Service pack version
  • 'CTPn', = Community Technology Preview version

Applies To

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

Example


select SERVERPROPERTY('ProductLevel') as 'Product Level'



ProductMajorVersion


Description

Get the major version.

Applies To

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

Example


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



ProductMinorVersion


Description

Get the minor version.

Applies To 

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

Example


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



ProductUpdateLevel


Description

Get the current Cumulative update installed name as CUn.

Applies To

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

Example


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



ProductVersion


Description

Get the product version as major.minor.build.revision.

Applies To

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

Example


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



BuildClrVersion


Description

Get the Version of the Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server.

Applies To

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

Example


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



Collation


Description

Get the name of the default collation for the server.

Applies To

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

Example


select SERVERPROPERTY('Collation') as 'Collation'



LCID


Description

Get the locale identifier (LCID) of the collation.

Applies To

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

Example


select SERVERPROPERTY('LCID') as 'LCID'



IsSingleUser


Description

Check if the Server is in single-user mode.

Applies To

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

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'



IsIntegratedSecurityOnly


Description

Check the integrated security mode.

Applies To

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

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'



IsHadrEnabled


Description

Check Always On availability groups is enabled or disabled.

Applies To

  • SQL Server 2012 ,2014,2016,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'



HadrManagerStatus


Description


Check the Always On availability groups manager status.

Applies To

  • SQL Server 2012 ,2014,2016,2017.

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'



IsAdvancedAnalyticsInstalled


Description

Check the Advanced Analytics status.

Applies To

  • SQL Server 2016,2017.

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'



IsClustered


Description

Check if the failover cluster is configured or not.

Applies To

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

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'



IsFullTextInstalled


Description

Check if The full-text and semantic indexing components are installed or not.

Applies To

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

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'



ProcessID


Description


Get the Process ID of the SQL Server service.


Applies To

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

Example


select SERVERPROPERTY('ProcessID') as 'ProcessID'



How to get the 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.
PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('MachineName') as 'MachineName'" -ServerInstance "epm\epmdb" -Username sa -Password *****



Download

Download the full query from TechNet Gallery at  Get The Detailed SQL Server Information.


Conclusion

In this article, we have learned How to get the SQL Server Information locally and remotely via SERVERPROPERTY?

Reference

SERVERPROPERTY (Transact-SQL)

Back To Top