SERVERPROPERTY (Transact-SQL)

propertyname

Is an expression that contains the property information to be returned for the server. propertyname can be one of the following values.

Property	Values returned
BuildClrVersion	Version of the Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server. Base data type: nvarchar(128)
Collation	Name of the default collation for the server. NULL = Input is not valid, or an error. Base data type: nvarchar(128)
CollationID	ID of the SQL Server collation. Base data type: int
ComparisonStyle	Windows comparison style of the collation. Base data type: int
ComputerNamePhysicalNetBIOS	NetBIOS name of the local computer on which the instance of SQL Server is currently running. For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster. On a stand-alone instance of SQL Server, this value remains constant and returns the same value as the MachineName property. Note If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property. NULL = Input is not valid, or an error. Base data type: nvarchar(128)
Edition	Installed product edition of the instance of SQL Server. Use the value of this property to determine the features and the limits, such as Compute Capacity Limits by Edition of SQL Server. 64-bit versions of the Database Engine append (64-bit) to the version. Returns: 'Enterprise Edition' ‘Enterprise Edition: Core-based Licensing’ 'Enterprise Evaluation Edition' ‘Business Intelligence Edition’ 'Developer Edition' 'Express Edition' 'Express Edition with Advanced Services' 'Standard Edition' 'Web Edition' Base data type: nvarchar(128)
EditionID	EditionID represents the installed product edition of the instance of SQL Server. Use the value of this property to determine features and limits, such as Compute Capacity Limits by Edition of SQL Server. 1804890536 = Enterprise 1872460670 = Enterprise Edition: Core-based Licensing 610778273= Enterprise Evaluation 284895786 = Business Intelligence -2117995310 = Developer -1592396055 = Express -133711905= Express with Advanced Services -1534726760 = Standard 1293598313 = Web Base data type: bigint
EngineEdition	Database Engine edition of the instance of SQL Server installed on the server. 1 = Personal or Desktop Engine (Not available in SQL Server 2005 and later versions.) 2 = Standard (This is returned for Standard, Web, and Business Intelligence.) 3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.) 4 = Express (This is returned for Express, Express with Tools and Express with Advanced Services) 5 = SQL Azure Base data type: int
HadrManagerStatus	Indicates whether the AlwaysOn Availability Groups manager has started. 0 = Not started, pending communication. 1 = Started and running. 2 = Not started and failed.
InstanceName	Name of the instance to which the user is connected. Returns NULL if the instance name is the default instance, if the input is not valid, or error. Base data type: nvarchar(128)
IsClustered	Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input is not valid, or an error. Base data type: int
IsFullTextInstalled	The full-text and semantic indexing components are installed on the current instance of SQL Server. 1 = Full-text and semantic indexing components are installed. 0 = Full-text and semantic indexing components are not installed. NULL = Input is not valid, or an error. Base data type: int
IsHadrEnabled	AlwaysOn Availability Groups is enabled on this server instance. 0 = The AlwaysOn Availability Groups feature is disabled. 1 = The AlwaysOn Availability Groups feature is enabled. Base data type: int For availability replicas to be created and run on an instance of SQL Server, AlwaysOn Availability Groups must be enabled on the server instance. For more information, see Enable and Disable AlwaysOn Availability Groups (SQL Server). Note The IsHadrEnabled property pertains only to AlwaysOn Availability Groups. Other high availability or disaster recovery features, such as database mirroring or log shipping, are unaffected by this server property.
IsIntegratedSecurityOnly	Server is in integrated security mode. 1 = Integrated security (Windows Authentication) 0 = Not integrated security. (Both Windows Authentication and SQL Server Authentication.) NULL = Input is not valid, or an error. Base data type: int
IsLocalDB	Server is an instance of SQL Server Express LocalDB.
IsSingleUser	Server is in single-user mode. 1 = Single user. 0 = Not single user NULL = Input is not valid, or an error. Base data type: int
LCID	Windows locale identifier (LCID) of the collation. Base data type: int
LicenseType	Unused. License information is not preserved or maintained by the SQL Server product. Always returns DISABLED. Base data type: nvarchar(128)
MachineName	Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server. NULL = Input is not valid, or an error. Base data type: nvarchar(128)
NumLicenses	Unused. License information is not preserved or maintained by the SQL Server product. Always returns NULL. Base data type: int
ProcessID	Process ID of the SQL Server service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance. NULL = Input is not valid or an error. Base data type: int
ProductVersion	Version of the instance of SQL Server, in the form of 'major.minor.build.revision'. Base data type: nvarchar(128)
ProductLevel	Level of the version of the instance of SQL Server. Returns one of the following: 'RTM' = Original release version 'SPn' = Service pack version 'CTP', = Community Technology Preview version Base data type: nvarchar(128)
ResourceLastUpdateDateTime	Returns the date and time that the Resource database was last updated. Base data type: datetime
ResourceVersion	Returns the version Resource database. Base data type: nvarchar(128)
ServerName	Both the Windows server and instance information associated with a specified instance of SQL Server. NULL = Input is not valid, or an error. Base data type: nvarchar(128)
SqlCharSet	The SQL character set ID from the collation ID. Base data type: tinyint
SqlCharSetName	The SQL character set name from the collation. Base data type: nvarchar(128)
SqlSortOrder	The SQL sort order ID from the collation Base data type: tinyint
SqlSortOrderName	The SQL sort order name from the collation. Base data type: nvarchar(128)
FilestreamShareName	The name of the share used by FILESTREAM.
FilestreamConfiguredLevel	The configured level of FILESTREAM access. For more information, see filestream access level.
FilestreamEffectiveLevel	The effective level of FILESTREAM access. This value can be different than the FilestreamConfiguredLevel if the level has changed and either an instance restart or a computer restart is pending. For more information, see filestream access level.

sql_variant

ServerName Property

EXEC sp_dropserver 'current_server_name';
GO
EXEC sp_addserver 'new_server_name', 'local';
GO

Version Properties

The following example uses the SERVERPROPERTY function in a SELECT statement to return information about the current server. This scenario is useful when there are multiple instances of SQL Server installed on a Windows server, and the client must open another connection to the same instance used by the current connection.

SELECT CONVERT(sysname, SERVERPROPERTY('servername'));
GO

The following example uses the SERVERPROPERTY function in a SELECT statement to return version information about the product.

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;
GO

Reference