Public/Get-MSSQLVersion.ps1
|
Function Get-MSSQLVersion { <# .Synopsis Checks remote registry for SQL Server Edition, Version and Instance. .Description Checks remote registry for SQL Server Edition, Version and Instance. .Parameter ComputerName The remote computer .Example Get-MSSQLVersion -ComputerName mymssqlsvr Get SQL Server version from "mymssqlsvr" .Example $list = cat .\sqlsvrs.txt $list | % { Get-MSSQLVersion $_ | select ServerName,Edition,Instance } Get SQL Server version from each computername in .\sqlsvrs.txt .INPUTS System.String .LINK about_functions_advanced .LINK about_CommonParameters #> [CmdletBinding()] [OutputType('System.Management.Automation.PSCustomObject')] Param ( [Parameter(ValueFromPipeline = $true)] [Alias('CN','Name','__SERVER','Server', 'IPAddress')] [String[]]$ComputerName = $ENV:COMPUTERNAME ) Begin { If (-not $PSBoundParameters.ContainsKey('Debug')) { $DebugPreference = $PSCmdlet.SessionState.PSVariable.GetValue('DebugPreference') } If (-not $PSBoundParameters.ContainsKey('Verbose')) { $VerbosePreference = $PSCmdlet.SessionState.PSVariable.GetValue('VerbosePreference') } If (-not $PSBoundParameters.ContainsKey('ErrorAction')) { $ErrorActionPreference = $PSCmdlet.SessionState.PSVariable.GetValue('ErrorActionPreference') } Write-Debug ('({0}) PsBoundParameters:' -f $MyInvocation.MyCommand) $PSBoundParameters.GetEnumerator() | ForEach-Object { Write-Debug ('({0}) {1}' -f $MyInvocation.MyCommand, $_ ) } Write-Debug ('({0}) DebugPreference: {1}' -f $MyInvocation.MyCommand, $DebugPreference) Write-Verbose ('{0}:: Function started' -f $MyInvocation.MyCommand) Write-Verbose ('{0}:: ErrorAction={1} ErrorActionPreference={2}' -f $MyInvocation.MyCommand, $ErrorAction, $ErrorActionPreference) } Process { # Test to see if the remote is up If (Test-Connection -ComputerName $ComputerName -Count 1 -Quiet) { # create an empty psobject (hashtable) $SqlVer = New-Object PSObject # add the remote server name to the psobj $SqlVer | Add-Member -MemberType NoteProperty -Name ServerName -Value $ComputerName # set key path for reg data $key = "SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" # i have no idea what this does, honestly, i stole it... $type = [Microsoft.Win32.RegistryHive]::LocalMachine # set up a .net call, uses the .net thingy above as a reference, could have just put # 'LocalMachine' here instead of the $type var (but this looks fancier :D ) $regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $ComputerName) # make the call $SqlKey = $regKey.OpenSubKey($key) If ($SqlKey -ne $null) { # parse each value in the reg_multi InstalledInstances ForEach ($instance In $SqlKey.GetValueNames()) { $instName = $SqlKey.GetValue("$instance") # read the instance name $instKey = $regKey.OpenSubkey("SOFTWARE\Microsoft\Microsoft SQL Server\$instName\Setup") # sub in instance name # add stuff to the psobj $SqlVer | Add-Member -MemberType NoteProperty -Name Edition -Value $instKey.GetValue("Edition") -Force # read Ed value $SqlVer | Add-Member -MemberType NoteProperty -Name Version -Value $instKey.GetValue("Version") -Force # read Ver value $SqlVer | Add-Member -MemberType NoteProperty -Name Instance -Value $SqlKey.GetValue("$instance") -Force # read Instance value # return an object, useful for many things $SqlVer | Format-Table -AutoSize } } Else { Write-Verbose "$Computername SQL Server not installed!" } } Else { Throw "Server $ComputerName unavailable..." } # if the connection test fails } End { Write-Verbose ('{0}:: Function ended' -f $MyInvocation.MyCommand) } } |