SQL Version and Stats Dump

SQL-Version.sql

SELECT
	@@VERSION AS '@@VERSION',
	CASE 
		WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion'))	 LIKE '8%' THEN 'SQL Server 2000'
		WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '9%' THEN 'SQL Server 2005'
		WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.0%' THEN 'SQL Server 2008'
		WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.5%' THEN 'SQL Server 2008 R2'
		WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '11%' THEN 'SQL Server 2012'
		WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '12%' THEN 'SQL Server 2014'
		WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '13%' THEN 'SQL Server 2016'
		WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '14%' THEN 'SQL Server 2017'
		WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '15%' THEN 'SQL Server 2019'
		WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '16%' THEN 'SQL Server 2022'
		ELSE SERVERPROPERTY('ProductVersion')
	END AS ProductVersion,
	SERVERPROPERTY('ProductLevel') AS ProductLevel,
	SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
	SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion,
	SERVERPROPERTY('ProductBuild') AS ProductBuild,
	SERVERPROPERTY('Edition') AS Edition,
	SERVERPROPERTY('MachineName') AS MachineName,
	SERVERPROPERTY('ServerName') AS ServerName,
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS,
-- https://learn.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver16
-- Generated by ChatGPT but it didn't quite get all the translations right as per the documentation :/
	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 'Azure Synapse Analytics'
		WHEN 8 THEN 'Azure SQL Managed Instance'
		WHEN 9 THEN 'Azure SQL Edge'
		WHEN 11 THEN 'Azure Synapse serverless SQL pool'
	ELSE SERVERPROPERTY('EngineEdition')
		END AS EngineEdition,
	CASE SERVERPROPERTY('EditionID')
		WHEN 1804890536 THEN 'Enterprise'
		WHEN 1872460670 THEN 'Enterprise Edition: Core-based Licensing'
		WHEN 610778273 THEN 'Enterprise Evaluation'
		WHEN 284895786 THEN 'Business Intelligence'
		WHEN -2117995310 THEN 'Developer'
		WHEN -1592396055 THEN 'Express'
		WHEN -133711905 THEN 'Express with Advanced Services'
		WHEN -1534726760 THEN 'Standard'
		WHEN 1293598313 THEN 'Web'
		WHEN 1674378470 THEN 'SQL Database or Azure Synapse Analytics'
		WHEN -1461570097 THEN 'Azure SQL Edge Developer'
		WHEN 1994083197 THEN 'Azure SQL Edge'
	ELSE SERVERPROPERTY('EditionID')
		END AS EditionID,
	SERVERPROPERTY('InstanceName') AS InstanceName,
	SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled,
	CASE SERVERPROPERTY('ProductBuildType')
		WHEN 'OD' THEN 'On Demand release a specific customer'
		WHEN 'GDR' THEN 'General Distribution Release released through Windows Update'
	ELSE 'Not applicable'
		END AS ProductBuildType,
	SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
	SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference;

-- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-host-info-transact-sql?view=sql-server-ver16#examples
SELECT * FROM sys.dm_os_host_info; 

-- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-windows-info-transact-sql?view=azuresqldb-current#examples
SELECT * FROM sys.dm_os_windows_info; 

-- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-info-transact-sql?view=sql-server-ver16&viewFallbackFrom=azuresqldb-current
SELECT * FROM sys.dm_os_sys_info;

-- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact-sql?view=sql-server-ver16
SELECT * FROM sys.dm_os_sys_memory;

GO