SQL Server Details

-- ============================================================================
-- SQL Server Details
--
-- ## SQL Product Version to Product Name Mapping:
--
-- | Version Pattern |    Product Name    |
-- |-----------------|--------------------|
-- | 16.0.x.x        | SQL Server 2022    |
-- | 15.0.x.x        | SQL Server 2019    |
-- | 14.0.x.x        | SQL Server 2017    |
-- | 13.0.x.x        | SQL Server 2016    |
-- | 12.0.x.x        | SQL Server 2014    |
-- | 11.0.x.x        | SQL Server 2012    |
-- | 10.50.x.x       | SQL Server 2008 R2 |
-- | 10.00.x.x       | SQL Server 2008    |
-- | 9.00.x.x        | SQL Server 2005    |
-- | 8.00.x.x        | SQL Server 2000    |
--
-- How to determine the version and edition of SQL Server and its components (https://support.microsoft.com/en-us/kb/321185)
--
-- Snippet: https://jonlabelle.com/snippets/view/sql/sql-server-details
-- ============================================================================

--
-- Get software and version info line:
declare @sqlVersion nvarchar(400);

set @sqlVersion = @@version;
set @sqlVersion = ltrim(rtrim(left(@sqlVersion, charindex(char(9), @sqlVersion) - 1))); -- only grab the first line
set @sqlVersion = ltrim(rtrim(replace(replace(@sqlVersion, char(13), ''), char(10), ''))); -- strip line endings

--
-- Try to get the system configured timezone:
declare @TimeZone varchar(50);

begin try
    exec master.dbo.xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
        @value_name = 'TimeZoneKeyName',
        @value = @TimeZone out;
end try
begin catch
    set @TimeZone = 'Unknown';
end catch;

--
-- Main
-- =========================================================================
select serverproperty('MachineName') as "Hostname",
    serverproperty('ServerName') as "ServerName",
    @sqlVersion as "SQLServerVersion",
    case serverproperty('EngineEdition')
        when 1
             then 'Desktop'
        when 2
             then 'Standard'
        when 3
             then 'Enterprise'
        when 4
             then 'Express'
        when 5
             then 'Azure'
        else 'Unknown'
    end as "EngineEdition",
    serverproperty('Edition') as "Edition",
    @@microsoftversion / 0x01000000 as "Version",
    serverproperty('ProductVersion') as "VersionBuild",
    serverproperty('BuildClrVersion') as "CLRBuildVersion",
    serverproperty('ProductLevel') as "ProductLevel",
    serverproperty('InstanceName') as "InstanceName",
    si.cpu_count as "CPUs",
    --physical_memory_in_bytes / 1048576 as 'RAM',
    serverproperty('IsClustered') as "Clustered",
    serverproperty('Collation') as "Collation",
    serverproperty('CollationID') as "CollationID",
    serverproperty('ComparisonStyle') as "ComparisonStyle",
    serverproperty('LCID') as "LCID",
    serverproperty('SqlCharSet') as "SqlCharSetID",
    serverproperty('SqlCharSetName') as "SqlCharSetName",
    serverproperty('SqlSortOrderName') as "SqlSortOrderName",
    serverproperty('IsFullTextInstalled') as "FullTextInstalled",
    serverproperty('IsIntegratedSecurityOnly') as "IntegratedSecurityModeOnly",
    serverproperty('IsSingleUser') as "SingleUserMode",
    serverproperty('ProcessID') as "SQLServerProcessID",
    serverproperty('ResourceLastUpdateDateTime') as "ResourceLastUpdateDateTime",
    serverproperty('ResourceVersion') as "ResourceVersion",
    os.physical_memory_in_use_kb / 1024 as "PhysicalMemoryInUse_MB",
    os.locked_page_allocations_kb / 1024 as "LockedPageAllocations_MB",
    os.total_virtual_address_space_kb / 1024 as "TotalVirtualAddressSpace_MB",
    os.process_physical_memory_low as "IsProcessPhysicalMemoryLow",
    os.process_virtual_memory_low as "IsProcessVirtualMemoryLow",
    @TimeZone as "TimeZone"
from sys.dm_os_sys_info as si,
    sys.dm_os_process_memory as os;