Search code examples
sql-servert-sqlpowershellsqlcmd

Retrieve all instances in sql server

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.

Solution

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