다음을 통해 공유

T-SQL: Get SQL Server Instance Information Using SERVERPROPERTY

  • 아티클

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'

https://gallery.technet.microsoft.com/site/view/file/180139/1/SERVERPROPERTY('ComputerNamePhysicalNetBIOS').png

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'

https://gallery.technet.microsoft.com/site/view/file/180140/1/SERVERPROPERTY('ServerName').png

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'

https://gallery.technet.microsoft.com/site/view/file/180141/1/SERVERPROPERTY('InstanceName').png

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'

https://gallery.technet.microsoft.com/site/view/file/180142/1/SERVERPROPERTY('InstanceDefaultDataPath').png

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'

https://gallery.technet.microsoft.com/site/view/file/180143/1/SERVERPROPERTY('InstanceDefaultLogPath').png

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'

https://gallery.technet.microsoft.com/site/view/file/180144/1/SERVERPROPERTY('Edition').png

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'

https://gallery.technet.microsoft.com/site/view/file/180145/1/SERVERPROPERTY('EditionID').png

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

https://gallery.technet.microsoft.com/site/view/file/180146/1/SERVERPROPERTY('EngineEdition').png

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'

https://gallery.technet.microsoft.com/site/view/file/180147/1/SERVERPROPERTY('ProductBuild').png

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'

https://gallery.technet.microsoft.com/site/view/file/180148/1/SERVERPROPERTY('ProductBuildType').png

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'

https://gallery.technet.microsoft.com/site/view/file/180149/1/SERVERPROPERTY('ProductLevel').png

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'

https://gallery.technet.microsoft.com/site/view/file/180150/1/SERVERPROPERTY('ProductMajorVersion').png

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'

https://gallery.technet.microsoft.com/site/view/file/180151/1/SERVERPROPERTY('ProductMinorVersion').png

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'

https://gallery.technet.microsoft.com/site/view/file/180180/1/SERVERPROPERTY('ProductUpdateLevel').png

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'

https://gallery.technet.microsoft.com/site/view/file/180153/1/SERVERPROPERTY('ProductVersion').png

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'

https://gallery.technet.microsoft.com/site/view/file/180154/1/SERVERPROPERTY('BuildClrVersion').png

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'

https://gallery.technet.microsoft.com/site/view/file/180155/1/SERVERPROPERTY('Collation').png

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'

https://gallery.technet.microsoft.com/site/view/file/180156/1/SERVERPROPERTY('LCID').png

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'

https://gallery.technet.microsoft.com/site/view/file/180157/1/SERVERPROPERTY('IsSingleUser').png

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'

https://gallery.technet.microsoft.com/site/view/file/180158/1/SERVERPROPERTY('IsIntegratedSecurityOnly').png

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'

https://gallery.technet.microsoft.com/site/view/file/180159/1/SERVERPROPERTY('IsHadrEnabled').png

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'

https://gallery.technet.microsoft.com/site/view/file/180160/1/SERVERPROPERTY('HadrManagerStatus').png

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'

https://gallery.technet.microsoft.com/site/view/file/180161/1/SERVERPROPERTY('IsAdvancedAnalyticsInstalled').png

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'

https://gallery.technet.microsoft.com/site/view/file/180162/1/SERVERPROPERTY('IsClustered').png

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'

https://gallery.technet.microsoft.com/site/view/file/180163/1/SERVERPROPERTY('IsFullTextInstalled').png

ProcessID

Description

Get the Process ID of the SQL Server service.

https://gallery.technet.microsoft.com/site/view/file/180164/1/ProcessID.png

Applies To

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

Example

select SERVERPROPERTY('ProcessID') as  'ProcessID'

https://gallery.technet.microsoft.com/site/view/file/180165/1/SERVERPROPERTY('ProcessID').png

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 *****

https://gallery.technet.microsoft.com/site/view/file/180166/1/Invoke-Sqlcmd.png

Download

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

https://i1.gallery.technet.s-msft.com/get-the-detailed-sql-f1e0379f/image/file/180334/1/get%20sql%20server%202016%20information.gif

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