Last active
June 22, 2023 00:37
-
-
Save ethzero/5c83e66e04b688674b3da58ab13c0fc7 to your computer and use it in GitHub Desktop.
SQL Version and Stats Dump
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment