SERVERPROPERTY - information about the server instance in SQL Server

SERVERPROPERTY (PropertyName)

Returns property information about the server instance in SQL Server 2008 R2.

See the properties list in:

http://msdn.microsoft.com/en-us/library/ms174396.aspx

Example:

SELECT

SERVERPROPERTY('servername') AS servername,

SERVERPROPERTY('Edition') AS Edition,

SERVERPROPERTY('IsSingleUser') AS IsSingleUser

Select all properties of SERVERPROPERTY():

SELECT 

SERVERPROPERTY('BuildClrVersion') as BuildClrVersion,

SERVERPROPERTY('Collation') as Collation,

SERVERPROPERTY('CollationID') as CollationID,

SERVERPROPERTY('ComparisonStyle') as ComparisonStyle,

SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ComputerNamePhysicalNetBIOS,

SERVERPROPERTY('Edition') as Edition,

SERVERPROPERTY('EditionID') as EditionID,

SERVERPROPERTY('EngineEdition') as EngineEdition,

SERVERPROPERTY('FilestreamConfiguredLevel') as FilestreamConfiguredLevel,

SERVERPROPERTY('FilestreamEffectiveLevel') as FilestreamEffectiveLevel,

SERVERPROPERTY('FilestreamShareName') as FilestreamShareName,

SERVERPROPERTY('HadrManagerStatus') as HadrManagerStatus,

SERVERPROPERTY('InstanceDefaultBackupPath') as InstanceDefaultBackupPath,

SERVERPROPERTY('InstanceDefaultDataPath') as InstanceDefaultDataPath,

SERVERPROPERTY('InstanceDefaultLogPath') as InstanceDefaultLogPath,

SERVERPROPERTY('InstanceName') as InstanceName,

SERVERPROPERTY('IsAdvancedAnalyticsInstalled') as IsAdvancedAnalyticsInstalled,

SERVERPROPERTY('IsBigDataCluster') as IsBigDataCluster,

SERVERPROPERTY('IsClustered') as IsClustered,

SERVERPROPERTY('IsFullTextInstalled') as IsFullTextInstalled,

SERVERPROPERTY('IsHadrEnabled') as IsHadrEnabled,

SERVERPROPERTY('IsIntegratedSecurityOnly') as IsIntegratedSecurityOnly,

SERVERPROPERTY('IsLocalDB') as IsLocalDB,

SERVERPROPERTY('IsPolyBaseInstalled') as IsPolyBaseInstalled,

SERVERPROPERTY('IsSingleUser') as IsSingleUser,

SERVERPROPERTY('IsTempDbMetadataMemoryOptimized') as IsTempDbMetadataMemoryOptimized,

SERVERPROPERTY('IsXTPSupported') as IsXTPSupported,

SERVERPROPERTY('LCID') as LCID,

SERVERPROPERTY('LicenseType') as LicenseType,

SERVERPROPERTY('MachineName') as MachineName,

SERVERPROPERTY('NumLicenses') as NumLicenses,

SERVERPROPERTY('ProcessID') as ProcessID,

SERVERPROPERTY('ProductBuild') as ProductBuild,

SERVERPROPERTY('ProductBuildType') as ProductBuildType,

SERVERPROPERTY('ProductLevel') as ProductLevel,

SERVERPROPERTY('ProductMajorVersion') as ProductMajorVersion,

SERVERPROPERTY('ProductMinorVersion') as ProductMinorVersion,

SERVERPROPERTY('ProductUpdateLevel') as ProductUpdateLevel,

SERVERPROPERTY('ProductUpdateReference') as ProductUpdateReference,

SERVERPROPERTY('ProductVersion') as ProductVersion,

SERVERPROPERTY('ResourceLastUpdateDateTime') as ResourceLastUpdateDateTime,

SERVERPROPERTY('ResourceVersion') as ResourceVersion,

SERVERPROPERTY('ServerName') as ServerName,

SERVERPROPERTY('SqlCharSet') as SqlCharSet,

SERVERPROPERTY('SqlCharSetName') as SqlCharSetName,

SERVERPROPERTY('SqlSortOrder') as SqlSortOrder,

SERVERPROPERTY('SqlSortOrderName') as SqlSortOrderName

;