1 min read

Powershell - Get SQL Server information

Powershell - Get SQL Server information

Here's a script that i put together, based on Jason Sandys VBScript, in order to get information from an SQL server.
I used it with MECM "Run Script" function in order to run it against an collection of SQL Servers managed by Configuration Manager, and thus getting all the info in the outputs from each.

eg. running it locally on an SQL Server will output something like this:

The script

$Instances = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

#Gather server information
Foreach($Instance in $Instances){
    $Inst = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$Instance
    $Edition = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$Inst\Setup").Edition
    $Version = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$Inst\Setup").Version
    $PatchLevel = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$Inst\Setup").PatchLevel
    $TCPPort = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$Inst\MSSQLServer\SuperSocketNetLib\Tcp\IPAll").TcpPort
    $Architecture = (Get-WMIObject win32_operatingsystem).OSArchitecture
    $Collation = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$Inst\Setup").Collation
}

#Get SQL Server Name
$Name = (Get-WMIObject win32_operatingsystem).CSName

#Output gathered info
Write-Output "
    Name: $Name
    Edition: $Edition
    SQLVersion: $Version 
    PatchLevel: $PatchLevel
    TCPPort: $TCPPort
    Collation: $Collation
    Architecture: $Architecture
    "