View SQL Server Properties

This script gives you basic information on a SQL Instance such as version, edition, server collation, and instance level switch info.

SELECT
	 @@SERVERNAME									AS ServerName
	,@@SERVICENAME									AS InstanceName
	,SERVERPROPERTY('MachineName')					AS LogicalMachineName
	,SERVERPROPERTY('ComputerNamePhysicalNetBIOS')	AS PhysicalComputerName
	,SERVERPROPERTY('ProductVersion')				AS ProductVersion
	,CASE 
		WHEN CONVERT(varchar(128),SERVERPROPERTY('productversion')) LIKE '7%'	THEN '7.0'
		WHEN CONVERT(varchar(128),SERVERPROPERTY('productversion')) LIKE '8%'	THEN '2000'
		WHEN CONVERT(varchar(128),SERVERPROPERTY('productversion')) LIKE '9%'	THEN '2005'
		WHEN CONVERT(varchar(128),SERVERPROPERTY('productversion')) LIKE '10.0%'THEN '2008'
		WHEN CONVERT(varchar(128),SERVERPROPERTY('productversion')) LIKE '10.5%'THEN '2008 R2'
		WHEN CONVERT(varchar(128),SERVERPROPERTY('productversion')) LIKE '11%'	THEN '2012'
		WHEN CONVERT(varchar(128),SERVERPROPERTY('productversion')) LIKE '12%'	THEN '2014'
		WHEN CONVERT(varchar(128),SERVERPROPERTY('productversion')) LIKE '13%'	THEN '2016'
		WHEN CONVERT(varchar(128),SERVERPROPERTY('productversion')) LIKE '14%'	THEN '2017'
		ELSE 'Unknown'
	 END											AS ProductMajorVersion
	,SERVERPROPERTY('ProductLevel')					AS ProductLevel
	,SERVERPROPERTY('ProductMinorVersion')			AS ProductMinorVersion
	,SERVERPROPERTY('ProductUpdateLevel')			AS ProductUpdateLevel
	,CASE SERVERPROPERTY('EngineEdition')
		WHEN 1 THEN 'Personal'
		WHEN 2 THEN 'Standard'
		WHEN 3 THEN 'Enterprise'
		WHEN 4 THEN 'Express'
		WHEN 5 THEN 'SQL Database'
		WHEN 6 THEN 'SQL Data Warehouse'
		WHEN 6 THEN 'Managed Instance'
		ELSE ''
	END												AS EngineEdition
    ,CASE 
        WHEN SERVERPROPERTY('EditionID') =  1804890536	THEN 'Enterprise'
		WHEN SERVERPROPERTY('EditionID') =  1872460670 	THEN 'Enterprise: Core-based Licensing'
        WHEN SERVERPROPERTY('EditionID') =  610778273	THEN 'Enterprise Evaluation'
		WHEN SERVERPROPERTY('EditionID') =  284895786 	THEN 'Business Intelligence'
        WHEN SERVERPROPERTY('EditionID') = -2117995310	THEN 'Developer'
        WHEN SERVERPROPERTY('EditionID') = -1592396055	THEN 'Express'
        WHEN SERVERPROPERTY('EditionID') =  4161255391	THEN 'Express with Advanced Services'
        WHEN SERVERPROPERTY('EditionID') = -1534726760	THEN 'Standard'
		WHEN SERVERPROPERTY('EditionID') =  1293598313 	THEN 'Web'
		WHEN SERVERPROPERTY('EditionID') =  1674378470 	THEN 'SQL Database or SQL Data Warehouse'
        WHEN SERVERPROPERTY('EditionID') =  1044790755	THEN 'Windows Embedded SQL'
     END											AS ProductEdition
	,SERVERPROPERTY('ResourceLastUpdateDateTime')	AS LastUpdated
	,SERVERPROPERTY('Collation')					AS Collation
	,(SELECT SUM(mf.size / 128 ) FROM sys.master_files AS mf WHERE type_desc = 'ROWS') AS TotalDatabaseSizeMb
	,(SELECT MAX(mf.size / 128 ) FROM sys.master_files AS mf WHERE type_desc = 'ROWS') AS LargestDatabaseMb
	,SERVERPROPERTY('InstanceDefaultDataPath')		AS InstanceDefaultDataPath
	,SERVERPROPERTY('InstanceDefaultLogPath')		AS InstanceDefaultLogPath
	,SERVERPROPERTY('IsFullTextInstalled')			AS IsFullTextInstalled
	,SERVERPROPERTY('IsIntegratedSecurityOnly')		AS IsIntegratedSecurityOnly
	,SERVERPROPERTY('IsClustered')					AS IsClustered
	,SERVERPROPERTY('IsHadrEnabled')				AS IsHadrEnabled
	,CASE 
		WHEN SERVERPROPERTY('IsHadrEnabled') = 1 
			AND SERVERPROPERTY('HadrManagerStatus') = 0 
		THEN 'Not started, pending communication.'
		WHEN SERVERPROPERTY('IsHadrEnabled') = 1 
			AND SERVERPROPERTY('HadrManagerStatus') = 1 
		THEN 'Started and running'
		WHEN SERVERPROPERTY('IsHadrEnabled') = 1 
			AND SERVERPROPERTY('HadrManagerStatus') = 2 
		THEN 'Not started and failed'
		ELSE 'NA'
	 END 											AS HadrManagerStatus;