Get a List of all SQL Instances on a Server

It is pretty common that I frequently am asked to take over a server with SQL on it sight unseen. Many times shops many not be fully aware if additional instances are or have been installed on their server. Since I am working on a set of scripts that we can use for discovery on new systems, I thought it would be handy to have a query that would give me a list of SQL instances with the version that are installed.

I found that this kind of information can be found in the registry which is accessible via a couple of undocumented extended procedures. Let me give the disclaimer right here…. Undocumented procedures from Microsoft are not supported and should not be used for regular production work. This is because they can change or be removed at any time. Now that I have stated that, the other obvious statement is that undocumented procedures can be pretty handy for some things, just use them with caution.

My query uses xp_regread and xp_instance_regnumvalues. Both will read values from the registry in the key values you point them at.

DECLARE @regpath NVARCHAR(128)
DECLARE @regkey NVARCHAR(500)
DECLARE @Inst VARCHAR(128)

CREATE TABLE #AllInstanceOnMachine (InstName VARCHAR(128), RegPathToUse VARCHAR(128), VersionFound VARCHAR(50))
CREATE TABLE #CurVer (RegValue VARCHAR(128), VersionFound VARCHAR(50))

INSERT INTO #AllInstanceOnMachine (InstName, RegPathToUse)
EXEC   master..xp_instance_regenumvalues
	@rootkey = N'HKEY_LOCAL_MACHINE',
	@key     = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'

DECLARE InstCur CURSOR FOR
SELECT InstName, RegPathToUse FROM #AllInstanceOnMachine

OPEN InstCur

WHILE (1=1)
BEGIN
	FETCH NEXT FROM InstCur 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 #AllInstanceOnMachine SET VersionFound = (SELECT VersionFound FROM #CurVer) WHERE InstName = @Inst

END
-- Close and deallocate the cursor
CLOSE InstCur
DEALLOCATE InstCur

SELECT * FROM #AllInstanceOnMachine

-- 8.0 = SQL 2000
-- 9.0 = SQL 2005
-- 10.0 = SQL 2008
-- 10.50 = SQL 2008 R2
-- 11.0 = SQL 2012
-- 12.0 = SQL 2014
-- 13.0 = SQL 2016
-- 14.0 = SQL 2017

DROP TABLE #AllInstanceOnMachine
DROP TABLE #CurVer

And, here are the results from one of our dev servers.

This only gives a list of the instances found in the registry. It does not tell you if the instances are actually running or not so there is still some investigation to be done. Of course I know you can always just open the SQL Configuration Manager to find things but then I would not have had the fun of making a cool query would I?!

If you ever need to find out exactly what build and service pack or CU you might have installed, you can always use https://sqlserverbuilds.blogspot.com/ for a comprehensive list.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!