PowerShell – Get SQL Server Instance Info from Hosts – With Pipeline input support

There is not much to say except that this supports pipeline input. Take a close look. PowerShell folks don’t read text, they just focus on the code…So, here it is.

#######################
<#
.SYNOPSIS
    Gets all the SQL Server instances on given host

.DESCRIPTION
    Is an advanced function with pipeline support. Allows custom credential to be passed in. See examples.

.INPUTS
    ComputerName
    Credential (optional)

.OUTPUTS
    None 

.EXAMPLE 

    Get-SqlInstanceInfo -ServerName BadServerName

.EXAMPLE 

    @('MyServer1','MyHostName2','ThatTestServerName') | Get-SqlInstanceInfo | ft

.NOTES
    Used pointers from refereced links and enhanced considerably by Jana

Version History
    v1.0  - Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com]

.LINK
    http://stackoverflow.com/questions/7516337/powershell-list-all-sql-instances-on-my-system
    http://www.orcsweb.com/blog/james/fun-with-powershell-how-to-get-sql-server-information/
#>

# get all sql instances, defaults to local machine, '.'
Function Get-SqlInstanceInfo {
    [CmdletBinding()]
    param
    (
        [Parameter(Position=0,
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true)]
        [Alias('HostName')]
        [Alias('cn')]
        [Alias('ComputerName')]
        [string]$ServerName = '.',

        [Parameter(Mandatory = $False,
                   Position=1,
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true)]
        [Alias('Cred')]
        [PSCredential] $Credential = $null
    )

    begin {}

    process
    {

        try
        {
            #If "-ErrorAction Stop" is not specified, upon error, control would not go to the Catch block!
            #  Alternatively, one can change the ErrorActionPreference...but that is not the right way to do this.

            if ($Credential)
            {
                $matches = gwmi win32_service -computerName $ServerName -ErrorAction Stop -Credential $Credential |
                                ?{$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe"}
            }
            else
            {
                $matches = gwmi win32_service -computerName $ServerName -ErrorAction Stop |
                                ?{$_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe"}
            }

            foreach ($match in $matches)
            {
                if ($match.Name.ToUpper() -eq "MSSQLSERVER")
                {
                    $match | Select-Object @{name="ComputerName";expression={$match.PSComputerName}}, `
                                        @{name="IsError";expression={'N'}}, `
                                        @{name="Error";expression={$null}}, `
                                        @{name="InstanceName";expression={'MSSQLSERVER'}}, `
                                        @{name="CompleteName";expression={$match.PSComputerName}}, `
                                        *
                }
                else
                {
                    #If it is not the default instance, Name will be in the form "MSSQL$SUNCT01" where "SUNCT01" is the instance name.
                    $instanceName = $match.Name.ToUpper().Replace('MSSQL$','')

                    $match | Select-Object @{name="ComputerName";expression={$match.PSComputerName}}, `
                                        @{name="IsError";expression={'N'}}, `
                                        @{name="Error";expression={$null}}, `
                                        @{name="InstanceName";expression={$instanceName}}, `
                                        @{name="CompleteName";expression={"$($match.PSComputerName)\$instanceName"}}, `
                                        *
                }
            }
        }
        catch
        {
            #This Exception object will become the output if there is an error
            New-Object -TypeName Exception -ArgumentList "[$ServerName] Error: $($_.Exception.Message)" |
                    Select-Object @{name="ComputerName";expression={$ServerName}}, `
                                        @{name="IsError";expression={'Y'}}, `
                                        @{name="Error";expression={$_.ToString()}}, `
                                        @{name="InstanceName";expression={$null}}, `
                                        @{name="CompleteName";expression={$null}}
        }
    }

    end {}

}

The “Output” column will have an Exception object containing the error if the attempt failed to fetch the instance information.

Also, isn’t this cool?

@('MyServer1','MyHostName2','ThatTestServerName') |
Get-SqlInstanceInfo |
Format-Table -AutoSize

The output looks something like this: