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)
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.