get physical memory in tsql Script on all SQL Versions

Question

Hi everyone what i need in the below statement is physical memory. Starting with SQL2012 i can get it with that statement:
select (physical_memory_in_bytes/1024/1024) as Physical_Mem_MB from sys.dm_os_sys_info previous versions i can get it whit that command:
select (physical_memory_KB/1024) as Physical_Mem_MB from sys.dm_os_sys_info Hence, how can i implement the abvoe statement if i want to run the script on all SQL Versions? With CASE THEN or IF, etc? Thanks.

SELECT 
serverproperty('MachineName') As 'HostName'
,createdate as 'InstCreateDate'

,serverproperty('instancename') as 'InstName',
CASE
WHEN left(cast(serverproperty('productversion') as varchar), 4) = '8.00' THEN 'SQL Server 2000'
WHEN left(cast(serverproperty('productversion') as varchar), 4) = '9.00' THEN 'SQL Server 2005'
WHEN left(cast(serverproperty('productversion') as varchar), 5) = '10.0.' THEN 'SQL Server 2008'
WHEN left(cast(serverproperty('productversion') as varchar), 5) = '10.00' THEN 'SQL Server 2008'
WHEN left(cast(serverproperty('productversion') as varchar), 5) = '10.50' THEN 'SQL Server R2 2008'
WHEN left(cast(serverproperty('productversion') as varchar), 4) = '11.0' THEN 'SQL Server 2012'
WHEN left(cast(serverproperty('productversion') as varchar), 4) = '12.0' THEN 'SQL Server 2014'
WHEN left(cast(serverproperty('productversion') as varchar), 4) = '13.0' THEN 'SQL Server 2016'
WHEN left(cast(serverproperty('productversion') as varchar), 4) = '14.0' THEN 'SQL Server 2017'
WHEN left(cast(serverproperty('productversion') as varchar), 4) = '15.0' THEN 'SQL Server 2019'
WHEN left(cast(serverproperty('productversion') as varchar), 4) = '16.0' THEN 'SQL Server 2022'
ELSE left(cast(serverproperty('productversion') as varchar), 5)
END AS SQLVersion
,serverproperty('productversion') AS 'ProductVersion'
,(SELECT  cpu_count  FROM sys.dm_os_sys_info) AS [CPUcount]
,GETDATE ( ) as dateRun
from sys.syslogins
where name like 'NT%AUTHORITY%SYSTEM%';

Answer 1

Hi @Andi Gra Referring to this doc: sys.dm_os_sys_info (Transact-SQL)

Try using If Else like this:

IF CAST(LEFT(CAST(serverproperty('productversion') AS varchar), 4)AS decimal(4,2)) >= 11
BEGIN
 --Query using (physical_memory_KB/1024) as Physical_Mem_MB
END
ELSE
BEGIN
  --Query using (physical_memory_in_bytes/1024/1024) as Physical_Mem_MB
END

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.