0

I would like to retrieve all the sql server istances on my sql server. I found this. With this sqlcmd utility I can retrieve the currently-running instance 

select @@servername
go

but how can I have a list of all the instances in my sql server?

edit the solution proposed in this postenter link description here doesn't work.

I however found a solution using powershell using SQL Server Management Object SMO.

Community
  • 1
  • 1
Hari
  • 191
  • 1
  • 2
  • 13
  • 1
    Possible duplicate of [How can I determine installed SQL Server instances and their versions?](http://stackoverflow.com/questions/141154/how-can-i-determine-installed-sql-server-instances-and-their-versions) – Backs Aug 22 '16 at 10:22
  • The answer given in the duplicate you postes is not working. Maybe it was useful for older versions? – Hari Aug 22 '16 at 10:36

1 Answers1

1

This will give you some idea - 

IF OBJECT_ID(N'tempdb.dbo.#SQLServiceNames', N'U') IS NOT NULL
BEGIN   DROP TABLE #SQLServiceNames END
CREATE TABLE #SQLServiceNames (SQL_ServiceName VARCHAR(100))

INSERT INTO #SQLServiceNames 
EXEC xp_cmdshell 'sc query type= service state= all |find "SQL" |find /V "DISPLAY_NAME" |find /V "AD" | find /V "Writer"

The refer below link: https://www.mssqltips.com/sqlservertip/2609/checking-sql-services-status--an-evolution-part-1/

p2k
  • 2,126
  • 4
  • 23
  • 39