DECLARE @Value sql_variant
SELECT @Value = value_in_use FROM master.sys.configurations WHERE [name] = 'xp_cmdshell'

IF @Value = 0
BEGIN
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
END

DECLARE @ServerNameList NVARCHAR(1000) = '"Server01","Server02","Server03"'
DECLARE @PowerShellFile NVARCHAR(300) = 'C:TempGet-PatchLevel.ps1' -- local folder on sql instance
DECLARE @PatchLevelFile NVARCHAR(100) = 'C:TempPatchLevel.txt'
DECLARE @OLE INT
DECLARE @FileID INT
DECLARE @Command NVARCHAR(4000)
DECLARE @SQLServerRegistryKeyPath VARCHAR (256), @SQLVersion VARCHAR (56), @InstanceName VARCHAR (56), @SQLPath VARCHAR (356)
DECLARE @InstalledInstances TABLE (InstanceName NVARCHAR(200))
DECLARE @Instances TABLE (MachineName sysname, InstanceName NVARCHAR(200), [Version] NVARCHAR(50))

SET NOCOUNT ON;

IF EXISTS(SELECT 1 from tempdb.sys.objects WHERE [name] like '#FileContents%')
DROP TABLE #FileContents
CREATE TABLE #FileContents (Instance nvarchar(200), [Server] nvarchar(200), [Version] nvarchar(200))

EXEC ('xp_cmdshell ''del "' + @PatchLevelFile + '"'', no_output')

EXEC ('xp_cmdshell ''del "' + @PowerShellFile + '"'', no_output')

-- get all installed sql instances
SET @Command = 'powershell.exe "Get-Service | Where-Object {$_.Name -like ''MSSQL$*''}"'
INSERT INTO @InstalledInstances (InstanceName)
EXEC xp_cmdshell @Command

DELETE FROM @InstalledInstances WHERE InstanceName IS NULL OR CHARINDEX('SQL Server', InstanceName, 1) = 0

UPDATE @InstalledInstances
SET InstanceName = RTRIM(LTRIM(SUBSTRING(InstanceName, CHARINDEX('(', InstanceName, 1) + 1, LEN(InstanceName) - CHARINDEX('(', InstanceName, 1) - 1)))

DECLARE InstalledInstances CURSOR FOR
SELECT InstanceName FROM @InstalledInstances

OPEN InstalledInstances

FETCH NEXT FROM InstalledInstances INTO @InstanceName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLServerRegistryKeyPath = N'SoftwareMicrosoftMicrosoft SQL Server' + @InstanceName + 'Setup'
-- Get SQLPath for installed instance
EXEC [master]..[xp_regread] N'HKEY_LOCAL_MACHINE'
,@SQLServerRegistryKeyPath
,'SQLPath'
,@SQLPath OUTPUT;

SET @SQLPath = RIGHT(@SQLPath, LEN(@SQLPath) - CHARINDEX('MSSQL', @SQLPath, 1) + 1)
SET @SQLPath = REPLACE(@SQLPath, 'MSSQL', '')
SET @SQLServerRegistryKeyPath = N'SoftwareMicrosoftMicrosoft SQL Server' + @SQLPath + 'Setup'
-- create PowerShell file
SET @Command = '
$ServerNameList = ' + @ServerNameList + '
#IF the output folder does not exist then create it
$OutputFolder = "c:Temp"
$DoesFolderExist = Test-Path $OutputFolder
$null = if (!$DoesFolderExist){MKDIR "$OutputFolder"}

foreach($ServerName in $ServerNameList)
{
$Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey(''LocalMachine'',$ServerName)
$RegKey = $Reg.OpenSubKey(''' + @SQLServerRegistryKeyPath + ''')
$Value = $RegKey.GetValue(''PatchLevel'')
''' + @InstanceName + ','' + $ServerName + '','' + $Value | out-file "' + @PatchLevelFile + '" -Append
}'
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @PowerShellFile, 8, 1

EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Command

EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE

SET @Command = 'powershell.exe -ExecutionPolicy Bypass -File "' + @PowerShellFile + '"'

EXEC xp_cmdshell @Command, no_output
-- delete PowerShell file
EXEC ('xp_cmdshell ''del "' + @PowerShellFile + '"'', no_output')
FETCH NEXT FROM InstalledInstances INTO @InstanceName
END
CLOSE InstalledInstances;
DEALLOCATE InstalledInstances;

-- insert data from output PatchLevel file
BULK INSERT #FileContents
FROM 'c:tempPatchLevel.txt'
WITH
(DATAFILETYPE = 'widechar',
FIELDTERMINATOR = ',',
ROWTERMINATOR = 'n'
)

SELECT DISTINCT @Command = STUFF( ( SELECT ',' + InstanceName + ''
FROM @InstalledInstances
ORDER BY InstanceName FOR XML PATH('') ), 1,1,'')

-- horizontal Pivot output
SET @Command = '
SELECT Server, ' + @Command + '
FROM
(SELECT [Instance], Server, Version
FROM #FileContents) p
PIVOT
(
MAX(Version)
FOR Instance IN
(' + @Command + ')
) AS pvt
ORDER BY pvt.Server'
EXEC(@Command)

-- vertical Pivot output
SET @Command = REPLACE(@ServerNameList, '"', '')
SET @Command = '
SELECT [Instance], ' + @Command + '
FROM
(SELECT [Instance], Server, Version
FROM #FileContents) p
PIVOT
(
MAX(Version)
FOR [Server] IN
(' + @Command + ')
) AS pvt
ORDER BY pvt.[Instance]'
EXEC(@Command)

-- Set it back
IF @Value = 0
BEGIN
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
END
GO