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 @GetInstancesWhen 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!
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
3 Comments. Leave new
Oh this is great – thanks Pinal!
Handy little script! Great job, Pinal!
Great query, thanks Pinal! …and a brilliant sample, how you can get information from registry via TSQL.