To find installed instances from T-SQL

CreateTable #SQLInstances
( Value nvarchar(100),
InstanceName nvarchar(100),
Data nvarchar(100))

Insertinto #SQLInstances
EXECUTExp_regread
@rootkey ='HKEY_LOCAL_MACHINE',
@key ='SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name ='InstalledInstances'


select instancename,Data from #SQLInstances