Select serverproperty('propertyname')

SERVERPROPERTY ( 'propertyname' )
Property	Values returned
BuildClrVersion	Version of the Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server .
Collation	Name of the default collation for the server.
CollationID	ID of the SQL Server collation.
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.
Edition	Installed product edition of the instance of SQL Server . Returns: 'Enterprise Edition', ‘Enterprise Edition: Core-based Licensing’ etc….
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 1674378470 = SQL Database or SQL Data Warehouse
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 Database 6 - SQL Data Warehouse
HadrManagerStatus	Applies to: SQL Server 2012 through SQL Server 2016 . Indicates whether the Always On Availability Groups manager has started. 0 = Not started, pending communication. 1 = Started and running. 2 = Not started and failed. NULL = Input is not valid, an error, or not applicable.
InstanceDefaultDataPath	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Name of the default path to the instance data files.
InstanceDefaultLogPath	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Name of the default path to the instance log files.
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. NULL = Input is not valid, an error, or not applicable.
IsClustered	Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input is not valid, an error, or not applicable.
IsFullTextInstalled	1 = Full-text and semantic indexing components are installed. 0 = Full-text and semantic indexing components are not installed. NULL = Input is not valid, an error, or not applicable.
IsHadrEnabled	Applies to: SQL Server 2012 through SQL Server 2016 . Always On Availability Groups is enabled on this server instance. 0 = The Always On Availability Groups feature is disabled. 1 = The Always On Availability Groups feature is enabled. NULL = Input is not valid, an error, or not applicable.
IsIntegratedSecurityOnly	1 = Integrated security (Windows Authentication) 0 = Not integrated security. (Both Windows Authentication and SQL Server Authentication.)
IsLocalDB	Server is an instance of SQL Server Express LocalDB.
IsPolybaseInstalled	Returns whether the server instance has the PolyBase feature installed. 0 = PolyBase is not installed. 1 = PolyBase is installed.
IsSingleUser	Server is in single-user mode. 1 = Single user. 0 = Not single user
IsXTPSupported	Applies to: SQL Server ( SQL Server 2014 through SQL Server 2016 ), SQL Database . Server supports In-Memory OLTP. 1= Server supports In-Memory OLTP. 0= Server does not supports In-Memory OLTP.
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.
ProcessID	Process ID of the SQL Server service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance.
ProductBuild	Applies to: SQL Server 2014 beginning October, 2015. The build number.
ProductBuildType	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Type of build of the current build. Returns one of the following: OD = On Demand release a specific customer. GDR = General Distribution Release released through windows update.
ProductLevel	Level of the version of the instance of SQL Server . Returns one of the following: 'RTM' = Original release version 'SPn' = Service pack version 'CTPn', = Community Technology Preview version
ProductMajorVersion	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. The major version.
ProductMinorVersion	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. The minor version.
ProductUpdateLevel	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Update level of the current build. CU indicates a cumulative update. Returns one of the following: CUn = Cumulative Update
ProductUpdateReference	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. KB article for that release.
ProductVersion	Version of the instance of SQL Server , in the form of 'major.minor.build.revision'.
ResourceLastUpdateDateTime	Returns the date and time that the Resource database was last updated.
ResourceVersion	Returns the version Resource database.
ServerName	Both the Windows server and instance information associated with a specified instance of SQL Server .
FilestreamShareName	The name of the share used by FILESTREAM.