How to list all the installed SQL Server on the Server using TSQL

The SQL Server instances are independent and do not know each other. The method is to query the registry to find the instances.

You can use the following Script to list all the installed sql server on the server using TSQL

Solution

=========

Set NoCount On

Declare @CurrID int,@ExistValue int, @MaxID int, @SQL nvarchar(1000)

Declare @TCPPorts Table (PortType nvarchar(180), Port int)

Declare @SQLInstances Table (InstanceID int identity(1, 1) not null primary key,

InstName nvarchar(180),

Folder nvarchar(50),

StaticPort int null,

DynamicPort int null,

Platform int null);

Declare @Plat Table (Id int,Name varchar(180),InternalValue varchar(50), Charactervalue varchar (50))

Declare @Platform varchar(100)

Insert into @Plat exec xp_msver platform

select @Platform = (select 1 from @plat where charactervalue like '%86%')

If @Platform is NULL

Begin

Insert Into @SQLInstances (InstName, Folder)

Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

Update @SQLInstances set Platform=64

End

else

Begin

Insert Into @SQLInstances (InstName, Folder)

Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';

Update @SQLInstances Set Platform=32

End

Declare @Keyexist Table (Keyexist int)

Insert into @Keyexist

Exec xp_regread'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

select @ExistValue= Keyexist from @Keyexist

If @ExistValue=1

Insert Into @SQLInstances (InstName, Folder)

Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',

N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

Update @SQLInstances Set Platform =32 where Platform is NULL

Select @MaxID = MAX(InstanceID), @CurrID = 1

From @SQLInstances

While @CurrID <= @MaxID

Begin

Delete From @TCPPorts

Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',

N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',

N''TCPDynamicPorts'''

From @SQLInstances

Where InstanceID = @CurrID

Insert Into @TCPPorts

Exec sp_executesql @SQL

Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',

N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',

N''TCPPort'''

From @SQLInstances

Where InstanceID = @CurrID

Insert Into @TCPPorts

Exec sp_executesql @SQL

Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',

N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',

N''TCPDynamicPorts'''

From @SQLInstances

Where InstanceID = @CurrID

Insert Into @TCPPorts

Exec sp_executesql @SQL

Select @SQL = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',

N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',

N''TCPPort'''

From @SQLInstances

Where InstanceID = @CurrID

Insert Into @TCPPorts

Exec sp_executesql @SQL

Update SI

Set StaticPort = P.Port,

DynamicPort = DP.Port

From @SQLInstances SI

Inner Join @TCPPorts DP On DP.PortType = 'TCPDynamicPorts'

Inner Join @TCPPorts P On P.PortType = 'TCPPort'

Where InstanceID = @CurrID;

Set @CurrID = @CurrID + 1

End

Select serverproperty('ComputerNamePhysicalNetBIOS') as ServerName, InstName, StaticPort, DynamicPort,Platform

From @SQLInstances

Set NoCount Off

Note: The above script may not return the desired results when run on 32 bit Edition of Sql Server which is installed on 64 bit Windows Server

Robert Davis (Solutions IQ) & Levi Justus,
Microsoft Sql Server