Post reply

List of all instance names in my server

  • August 23, 2018 at 8:45 am

    #415499

    In my computer I have installed 3  SQL Server instances (different editions). While connected to one of them and running

    select * from sys.servers

    I only get the name of one instance to which I am connected. How can I list all the instance names in my computer. 

    Thanks for your time!

  • August 23, 2018 at 8:52 am

    #2003064

    Hi,

    Can you use Powershell script as mentioned in this article?
    https://docs.microsoft.com/en-us/powershell/module/sqlserver/get-sqlinstance?view=sqlserver-ps

    Thanks.

  • August 23, 2018 at 8:54 am

    #2003065

    debasis.yours - Thursday, August 23, 2018 8:52 AM

    Hi,

    Can you use Powershell script as mentioned in this article?
    https://docs.microsoft.com/en-us/powershell/module/sqlserver/get-sqlinstance?view=sqlserver-ps

    Thanks.

    Thanks for help. Actually I want that names to use in a script so I'd like to find out names using T-SQL in order not to generate names manually and have cross system script.

  • August 23, 2018 at 9:05 am

    #2003067

    elea.grig - Thursday, August 23, 2018 8:54 AM

    debasis.yours - Thursday, August 23, 2018 8:52 AM

    Hi,

    Can you use Powershell script as mentioned in this article?
    https://docs.microsoft.com/en-us/powershell/module/sqlserver/get-sqlinstance?view=sqlserver-ps

    Thanks.

    Thanks for help. Actually I want that names to use in a script so I'd like to find out names using T-SQL in order not to generate names manually and have cross system script.

    I think you would need to do that by reading the registry in T-SQL. The basics of reading it would be:
    EXECUTE xp_regread 'HKEY_LOCAL_MACHINE',
    'SOFTWARE\Microsoft\Microsoft SQL Server',
    'InstalledInstances'

    You can insert those into a table variable and use that if it works better.

    Sue

  • August 23, 2018 at 9:09 am

    #2003068

    sys.servers will return results specific to whatever Instance you are connected to. It has no clue about other Instances which are installed on the machine. Recommended way is to make use of Powershell (something like Get-Service | Where-Object {$_.Name -like 'MSSQL$*'} ) to retrieve SQL services or you can pull the same information by querying registry from T-SQL.
    Script 1:
    DECLARE @GetInstances TABLE

    ( Value nvarchar(100),
     InstanceNames nvarchar(100),
     Data nvarchar(100))
    Insert into @GetInstances
    EXECUTE xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
    @value_name = 'InstalledInstances'
    Select InstanceNames from @GetInstances
    or 
    Script 2:
    Create Table #GetInstances

    ( Value nvarchar(100),
     InstanceNames nvarchar(100),
     Data nvarchar(100))
    Insert into #GetInstances
    EXECUTE xp_regread
      @rootkey = 'HKEY_LOCAL_MACHINE',
      @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
      @value_name = 'InstalledInstances'
     Select InstanceNames from #GetInstances
    drop table #GetInstances

    Script original source: https://www.sanssql.com/2011/03/t-sql-query-to-find-number-of-instances.html

  • August 23, 2018 at 9:15 am

    #2003069

    Sue_H - Thursday, August 23, 2018 9:05 AM

    elea.grig - Thursday, August 23, 2018 8:54 AM

    debasis.yours - Thursday, August 23, 2018 8:52 AM

    Hi,

    Can you use Powershell script as mentioned in this article?
    https://docs.microsoft.com/en-us/powershell/module/sqlserver/get-sqlinstance?view=sqlserver-ps

    Thanks.

    Thanks for help. Actually I want that names to use in a script so I'd like to find out names using T-SQL in order not to generate names manually and have cross system script.

    I think you would need to do that by reading the registry in T-SQL. The basics of reading it would be:
    EXECUTE xp_regread 'HKEY_LOCAL_MACHINE',
    'SOFTWARE\Microsoft\Microsoft SQL Server',
    'InstalledInstances'

    You can insert those into a table variable and use that if it works better.

    Sue

    Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply