List of all instance names in my server

Question

List of all instance names in my server

elea.grig

Ten Centuries

Points: 1231
More actions
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!

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

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

debasis.yours Hall of Fame Points: 3457 More actions · Answer 1

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.

elea.grig Ten Centuries Points: 1231 More actions · Answer 2

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.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

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

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 4

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

elea.grig Ten Centuries Points: 1231 More actions · Answer 5

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.