I needed to list out the all the OS versions for our SQL instances, there’s a couple of ways i found to do it (Im sure there’s plenty more) for the PowerShell and Command Prompt ways i load it into SQL via xp_cmdshell. Clearly the T-SQL is a much easier but depending on the output you need, you might want to use one of the others

 

/*
Return Windows OS 
*/

-- T-SQL 
SELECT RIGHT(@@version, LEN(@@version)- 3 -charindex (' ON ', @@VERSION)) AS 'T-SQL'


-- PowerShell with xp_cmdshell
CREATE TABLE #PS
    (
      PSoutput NVARCHAR(2000)
    );

DECLARE @posh NVARCHAR(2000);
SET @posh = 'powershell "(Get-WmiObject -class Win32_OperatingSystem).Caption"';

INSERT  INTO #PS
        ( PSoutput )
        EXEC xp_cmdshell @posh;
DELETE  FROM #PS
WHERE   PSoutput IS NULL  

SELECT  PSoutput as 'PowerShell with xp_cmdshell'
FROM    #PS

DROP TABLE #PS;


-- xp_cmdshell and Command Prompt
IF object_id('tempdb..#CMDResults', 'u') is not null
    DROP TABLE #CMDResults
CREATE TABLE #CMDResults ( line VARCHAR(MAX) )
INSERT  #CMDResults
        EXEC xp_cmdshell 'ver'

DELETE  #CMDResults
WHERE   line IS NULL

SELECT  line as 'xp_cmdshell and Command Prompt'
FROM    #CMDResults

DROP TABLE #CMDResults;

And the results look like below:

OSVersionFromSQL

Advertisements