SQL SERVER – How to Get List of SQL Server Instances Installed on a Machine Via T-SQL?

Earlier last year, I had posted a very interesting blog post which still today gets quite a lots of interest is SQL SERVER – How to Get List of SQL Server Instances Installed on a Machine?. In the recent Comprehensive Database Performance Health Check, the client wanted to get this one done by T-SQL only. I had replied to them via email but never got around to blog about it here.

Recently, I read the excellent comment by Zol’taan and realized that I should have blogged about it way earlier.

The question is How to Get List of SQL Server Instances Installed on a Machine Via T-SQL?

The answer is here:

DECLARE @GetInstances TABLE
( Value nvarchar(100),
InstanceNames nvarchar(100),
Data nvarchar(100))
Insert into @GetInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'
Select InstanceNames from @GetInstances

When you run above T-SQL command it goes to your registry and brings back the name of the instances.

Here is my earlier article on the same subject:

• SQL SERVER – Find Name of The SQL Server Instance
• SQL SERVER – Introduction to SERVERPROPERTY and example
• SQL SERVER – Find Hostname and Current Logged In User Name
• SQL SERVER – Q&A: SQL Clustering Virtual Server Name and Instance Name

Do post here any other tricks if you know and I will be happy to post it here with due credit to you. Trust me sharing is indeed good thing!

Reference: Pinal Dave (https://blog.sqlauthority.com)