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

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

  • Comments 6

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

 

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
  • 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/

  • 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

  • ------Getting List of SQl server instances from Network\LAN 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:'

  • very helpful tool.

  • 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.

  • 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

Page 1 of 1 (6 items)