Study your Server - Code extract DEMO

Obtain relevant data about information, configurations and performance from your SQL Servers

FROM: www.sqlguatemala.com

Version: 1.4

AUTHOR: Eduardo Pivaral (sqlguatemala.com)

MIT License

Get SQL Server General Information

This query is a code extract from Server Information Dashboard, it helps you get general information about your On-Premises SQL Server instance, the query is supported from SQL Server 2012 onwards.

In [1]:
SELECT SERVERPROPERTY('ServerName') AS ServerName
	,isnull(SERVERPROPERTY('InstanceName'), 'Default') AS InstanceName
	,SUBSTRING(@@VERSION,0,CHARINDEX('-',@@VERSION)-1) as [Version]
	,SERVERPROPERTY('EDITION') AS Edition
	,SERVERPROPERTY('ProductVersion') AS BuildNumber
	,SERVERPROPERTY('ProductLevel') AS ServicePackStatus
	,iif(SERVERPROPERTY('IsHadrEnabled') = 1, 'Yes', 'No') AS AlwaysOn
	,SERVERPROPERTY('InstanceDefaultDataPath') AS DataPath
	,SERVERPROPERTY('InstanceDefaultLogPath') AS LogPath
	,iif(SERVERPROPERTY('IsClustered') = 1, 'Yes', 'No') AS isClustered
	,iif(SERVERPROPERTY('IsIntegratedSecurityOnly') = 0, 'Windows and SQL Server Authentication', 'Windows Authentication') AS AuthenticationType
	,cpu_count AS numberProcessors
	,round(physical_memory_kb / 1024.0 / 1024.0, 2) AS [Physical Memory_GB]
	,sqlserver_start_time AS LastStartTime
	,iif(virtual_machine_type = 0, 'No', 'Yes') AS isVirtualServer
	,isnull(windows_release + ' ' + windows_service_pack_level, '') AS OS_release
	
FROM sys.dm_os_sys_info
	,sys.dm_os_windows_info;