CODEWITHSUNDEEP
sql-servert-sqlpowershellsqlcmd
Hari
Hari

Reputation: 301

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.

Upvotes: 0

Views: 362

Answers (1)

Pintu Kawar
Pintu Kawar

Reputation: 2156

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/

Upvotes: 1

Related Questions