SQL Server: Get the Detailed Information Via SERVERPROPERTY

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 the SERVERPROPERTY?

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

SERVERPROPERTY Syntax

SERVERPROPERTY ('propertyname')

propertyname is 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

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

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

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

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

Get the default path of data files.

Applies To

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

Example

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


InstanceDefaultLogPath

Get the default path of log files.

Applies To

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

Example

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


Edition

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

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

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


ProductBuild

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

Get the type of build name.

Applies To

  • SQL Server 2012 through the current version of 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

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('ProductMajorVersion') as 'ProductMajor Version'


ProductMinorVersion

Get the minor version.

Applies To 

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

Example

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


ProductUpdateLevel

Get the current Cumulative update installed name as CUn.

Applies To

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

Example

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


ProductVersion

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

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

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

Get the locale identifier (LCID) of the collation.

Applies To

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

Example

select SERVERPROPERTY('LCID') as 'LCID'


IsSingleUser

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

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

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

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

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

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

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

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s