Show all the installed SQL instances on a server

Have you ever wondered if there was more than one instance of SQL Server installed on a physical server but didn’t know how to find out quickly? Well this piece of TSQL below should help which uses the undocumented stored procedure xp_regread. It can be run from the default instance or any of the named instances and will return a value similar to the screen shot below:

DECLARE @ShowAllSQLInstances TABLE

(

       Value NVARCHAR(100)

       ,InstanceNames NVARCHAR(100)

       ,Data NVARCHAR(100)

       )
 

INSERT INTO @ShowAllSQLInstances

EXECUTE xp_regread @rootkey = 'HKEY_LOCAL_MACHINE'

       ,@key = 'SOFTWARE\Microsoft\Microsoft SQL Server'

       ,@value_name = 'InstalledInstances'

SELECT InstanceNames
FROM @ShowAllSQLInstances

Click to enlarge

[This piece of code is ideal for a Snippet.]