PowerShell - List all SQL instances on my system?

Question

Is there a Powershell command to list all SQL instances on my system? (MS SQL 2008)

Ohh my god! So many good answers! Thank you a lot! But now I´m a little bit confused. Which is the best answer? :-) — LaPhi, Sep 23 '11 at 13:57

score 29 · Accepted Answer · answered Sep 23 '11 at 13:17

29

Just another way of doing it...can be a little quicker than SQLPS to get a quick answer.


(get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

answered Sep 23 '11 at 13:17

score 7 · Answer 2 · edited Oct 27 '16 at 19:46

7

Import powershell sql server extensions:

 Import-Module SqlServer

Then do these commands

Set-Location SQLSERVER:\SQL\localhost
Get-ChildItem

edited Oct 27 '16 at 19:46

Warren P

65,725
40
181
316

answered Sep 22 '11 at 19:42

Aaron Bertrand

272,866
37
466
490

On my computer get-childitem from SQLSERVER:\SQL\localhost is really slow. I can't test in other computer now. Is a normal behavior? thanks – CB. Sep 23 '11 at 07:41
Better ways is: get-childitem | select instancename – CB. Sep 23 '11 at 08:24
Great! This also worked on remote systems: `Set-Location SQLSERVER:\SQL\remoteserver; Get-ChildItem`. – simlev Jan 23 '19 at 16:16

TechSpud · Answer 3 · 2014-08-05T17:42:30.087

I found that (for me at least) none of the above returned my SQL Express instance. I have 5 named instances, 4 full-fat SQL Server, 1 SQL Express. The 4 full-fat are included in the answers above, the SQL Express isn't. SO, I did a little digging around the internet and came across this article by James Kehr, which lists information about all SQL Server instances on a machine. I used this code as a basis for writing the function below.

# get all sql instances, defaults to local machine, '.'
Function Get-SqlInstances {
  Param($ServerName = '.')

  $localInstances = @()
  [array]$captions = gwmi win32_service -computerName $ServerName | ?{$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe"} | %{$_.Caption}
  foreach ($caption in $captions) {
    if ($caption -eq "MSSQLSERVER") {
      $localInstances += "MSSQLSERVER"
    } else {
      $temp = $caption | %{$_.split(" ")[-1]} | %{$_.trimStart("(")} | %{$_.trimEnd(")")}
      $localInstances += "$ServerName\$temp"
    }
  }
  $localInstances
}

score 4 · Answer 4 · answered Sep 23 '11 at 13:29

4

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$mach = '.'
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $mach
$m.ServerInstances

answered Sep 23 '11 at 13:29

Chad Miller

40,127
3
30
34

Can also use `$m = (Get-Item SQLServer:\SQL\$mach).ManagedComputer` – David Gardiner Apr 08 '14 at 01:50

score 2 · Answer 5 · answered Nov 24 '16 at 15:58

The System.Data.Sql namespace contains classes that support SQL Server-specific functionality.

By using the System.Data.Sql namespace you can get all MSSQL instances on a machine using this command in windows power shell: [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

CB. · Answer 6 · 2011-09-23T08:26:15.233

2

$a = "MyComputerName"

 [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | ? { $_.servername -eq $a}

Aaron method return a more sure response. Read Here about Instance.GetDataSources()

edited Sep 23 '11 at 08:26

answered Sep 22 '11 at 14:47

CB.

58,865
9
159
159

score 1 · Answer 7 · answered Jun 26 '20 at 08:11

This function it gonna return all the installed instances with the version details in a object list:

function ListSQLInstances {
$listinstances = New-Object System.Collections.ArrayList
$installedInstances = (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
foreach ($i in $installedInstances) {
    $instancefullname = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
    $productversion = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instancefullname\Setup").Version
    $majorversion = switch -Regex ($productversion) {
        '8' { 'SQL2000' }
        '9' { 'SQL2005' }
        '10.0' { 'SQL2008' }
        '10.5' { 'SQL2008 R2' }
        '11' { 'SQL2012' }
        '12' { 'SQL2014' }
        '13' { 'SQL2016' }    
        '14' { 'SQL2017' } 
        '15' { 'SQL2019' } 
        default { "Unknown" }
    }
    $instance = [PSCustomObject]@{
        Instance             = $i
        InstanceNameFullName = $instancefullname;
        Edition              = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instancefullname\Setup").Edition;
        ProductVersion       = $productversion;
        MajorVersion         = $majorversion;
    }
    $listinstances.Add($instance)
}

Return $listinstances
}

$instances = ListSQLInstances
foreach ($instance in $instances) {
    Write-Host $instance.Instance
}

PowerShell - List all SQL instances on my system?

7 Answers7

Linked