Freigeben über

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

  • Artikel

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

Comments

  • Anonymous
    March 20, 2009
    PingBack from http://blog.a-foton.ru/index.php/2009/03/20/how-to-list-all-the-installed-sql-server-on-the-server-using-tsql/

  • Anonymous
    September 15, 2010
    Hi Parikshit Savjani , This is great article I was trying since many days how to do for the same. Thanks a lot regards jayant dass 9313406257

  • Anonymous
    June 15, 2011
    ------Getting List of SQl server instances from NetworkLAN using SQL query -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO Declare @t table ( ServerName Varchar(50) ) insert into @t exec master..xp_cmdshelL 'sqlcmd -L' select ltrim(rtrim(ServerName)) From @t where ServerName is not null and ServerName <> '' and ServerName <> 'Servers:'

  • Anonymous
    September 10, 2013
    very helpful tool.

  • Anonymous
    April 09, 2014
    The comment has been removed

  • Anonymous
    August 05, 2014
    The solution from Pravin is the favorite because of the concerns SoHelpMeCodd has mentioned from the connect-item. Just one "little" correction to th pretty fine solution from Parvin: use SQLCMD -Lc to get rid of the TRIM in the SELECT-statement Lc will output a short simple list of all servers