CODEWITHSUNDEEP
sql-server-2008powershell
LaPhi
LaPhi

Reputation: 5887

PowerShell - List all SQL instances on my system?

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

Upvotes: 16

Views: 49411

Answers (8)

Dennis
Dennis

Reputation: 1784

If you like the list to contain accompanying network ports used by the instances, you can try this...

$MsSqlSvc = Get-CimInstance Win32_Service -Filter 'Name like "mssql*"'

$PortList = Get-NetTcpConnection | select -Unique LocalPort, OwningProcess

$SqlPorts = $MsSqlSvc | foreach {
  $ThisSvc = $_
  $PortList | where { $_.OwningProcess -in $ThisSvc.ProcessId }
}

$SqlPorts | select OwningProcess,
  @{
    l = 'SqlInstance';
    e = {
      $ThisPort = $_
      $MsSqlSvc | where { $_.ProcessId -eq $ThisPort.OwningProcess } | 
        select -ExpandProperty Name
    }
  },
  LocalPort

Upvotes: 0

TechSpud
TechSpud

Reputation: 3518

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 = Get-WmiObject win32_service -ComputerName $ServerName |
    where {
      $_.Name -match "mssql*" -and 
      $_.PathName -match "sqlservr.exe"
    } |
      foreach {$_.Caption}

  foreach ($Caption in $Captions) {
    if ($Caption -eq "MSSQLSERVER") {
      $LocalInstances += "MSSQLSERVER"
    } else {
      $Temp = $Caption |
        foreach {$_.split(" ")[-1]} |
          foreach {$_.trimStart("(")} |
            foreach {$_.trimEnd(")")}

      $LocalInstances += "$ServerName\$Temp"
    }

  }

  $localInstances
}

Upvotes: 6

CB.
CB.

Reputation: 60910

$a = "MyComputerName"

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

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

Upvotes: 2

Alexandru-Codrin Panaite
Alexandru-Codrin Panaite

Reputation: 734

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
}

Upvotes: 1

Ashraf Sada
Ashraf Sada

Reputation: 4895

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()

Upvotes: 3

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Import powershell sql server extensions:

 Import-Module SqlServer

Then do these commands

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

Upvotes: 8

Chad Miller
Chad Miller

Reputation: 41757

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

Upvotes: 4

user847990
user847990

Reputation:

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

Upvotes: 31

Related Questions