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

A registry query lists SQL Server Instances recorded on the Windows host. A customer wanted a T-SQL-only method.

SELECT SERVERPROPERTY('MachineName') AS MachineName,
       SERVERPROPERTY('ServerName') AS ServerName,
       SERVERPROPERTY('InstanceName') AS InstanceName;
-- Historical Windows registry inspection, not a portable supported DMV:
DECLARE @Instances TABLE (Value nvarchar(100), InstanceNames nvarchar(100), Data nvarchar(100));
INSERT @Instances
EXEC master.dbo.xp_regread
    @rootkey = N'HKEY_LOCAL_MACHINE',
    @key = N'SOFTWARE\Microsoft\Microsoft SQL Server',
    @value_name = N'InstalledInstances';
SELECT InstanceNames FROM @Instances;

Zol’taan’s comment reminded me to share this technique. It reads the SQL Server host’s registry, rather than the SSMS workstation. Registry view, installation state and permissions affect the result. xp_regread isn’t a documented portable catalog contract.

Verify registry results against installed services or a Setup discovery report. SERVERPROPERTY answers a separate question about the connected instance. InstanceName is NULL for a default instance. Neither query discovers every reachable SQL Server.

The earlier instance-name resources provide related approaches. Don’t grant broad privileges solely to make a registry helper work. Use an approved discovery method for the actual inventory scope.

Related reading

• SQL SERVER – How to Get List of SQL Server Instances Installed on a Machine?
• Comprehensive Database Performance Health Check
• Zol’taan
• 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

A local instance inventory is not network discovery, it is information about the connected host.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.