Here is a script to list the available instances of a SQL Server.

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

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!