Powershell script to find SQL Server Instances on a Server

Question: How can I find the SQL Server Instances on a server using Powershell ? Either the local server or remote SQL Servers.

The script should be based on  reporting the name after the MSSQL$   . For example , if the SQL Server Instance is called MSSQL$MYSERVER1  , than only MYSERVER1 should be returned

Answer: You can use this Powershell script as a basis to create a script . Place the  code in a powershell script file , and then execute 

.\Find_SQL_Server_Instances_on_server.ps1 -server MYSERVER1

param (
  
     [Parameter(Mandatory=$true)]
     [string]$server
         
)

$SERVER=$server

function getSQLInstanceOnServer ([string]$SERVER) {
    $services = Get-Service -Computer $SERVER
    $services = $services | ? DisplayName -like "SQL Server (*)"
        try {
        $instances = $services.Name | ForEach-Object {($_).Replace("MSSQL`$","")}
    }catch{
        # if no instances are found return 
        return -1
    }
    return $instances
}

getSQLInstanceOnServer $SERVER

You can also read these posts for some extra ways to enhance your Powershell scripts or as alternatives to the above . 

Powershell script - Get the SQL Server Instance status with Get-Service

List SQL Server Instances using Powershell and Get-ItemProperty

List SQL Server Instances with Powershell remote registry search