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.

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

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

Solarwinds

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:

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)

Solarwinds
,
Previous Post
SQL SERVER – How to DROP Multiple Tables Using Single DROP Statement?
Next Post
SQL SERVER – Finding Compression Ratio of Backup

Related Posts

3 Comments. Leave new

Leave a Reply

Menu