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


 

Comments (6)

  1. Jayant Das says:

    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

  2. Pravin Wagh says:

    ——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:'

  3. Cosmo says:

    very helpful tool.

  4. billhol@hotmail.com says:

    From connect.microsoft.com/…/xp-regread:

    {

    xp_regread is not supported nor should it be used. The security situation you're running into is by design and won't be changed.

    -Richard Waymire

    Program Manager, SQL Server

    }

    Caveat emptor: What works today may not work tomorrow.

    In contrast to Parikshit's "tip", Pravin's xp_cmdshell is both documented and supported (even so, restricting access to xp_cmdshell is a likely concern). Should reading registries truly be needed (instead of command line calls to sqlcmd), consider reg.exe (or other command line utilities) which be called from xp_cmdshell.

  5. Uwe Ricken says:

    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

Skip to main content