Script
DECLARE @regpath NVARCHAR(128)
DECLARE @regkey NVARCHAR(500)
DECLARE @Inst VARCHAR(128)
DROP TABLE IF EXISTS #AllInstancesOnMachine, #CurVer
CREATE TABLE #AllInstancesOnMachine (InstanceName VARCHAR(128), RegPathToUse VARCHAR(128), MajorVersionFound VARCHAR(50))
CREATE TABLE #CurVer (RegValue VARCHAR(128), VersionFound VARCHAR(50))
INSERT INTO #AllInstancesOnMachine (InstanceName, RegPathToUse)
EXEC master..xp_instance_regenumvalues
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'
DECLARE VersionCursor CURSOR FOR
SELECT InstanceName, RegPathToUse FROM #AllInstancesOnMachine
OPEN VersionCursor
WHILE (1=1)
BEGIN
FETCH NEXT FROM VersionCursor INTO @Inst, @regpath
IF @@FETCH_STATUS < 0 BREAK
SET @regkey = N'Software\Microsoft\Microsoft SQL Server\'
+@regpath+'\MSSQLSERVER\CurrentVersion'
TRUNCATE TABLE #CurVer
INSERT INTO #CurVer (RegValue, VersionFound)
EXECUTE master.sys.xp_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = @regkey,
@value_name = N'CurrentVersion'
UPDATE #AllInstancesOnMachine SET MajorVersionFound =
(SELECT CASE Parsename(VersionFound,4) when 10 then 'SQL 2008 or 2008 R2'
when 11 then 'SQL 2012'
when 12 then 'SQL 2014'
when 13 then 'SQL 2016'
when 14 then 'SQL 2017' End FROM #CurVer)
WHERE InstanceName = @Inst
END
CLOSE VersionCursor
DEALLOCATE VersionCursor
SELECT * FROM #AllInstancesOnMachine