THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Get a Quick Review of SQL Server Information

It can be tedious to keep a list of the version, edition or other information on each of the servers you manage. SMO provides an easy way to return this information, and PowerShell provides a quick way to get to SMO.

Sometimes you may not know if a server is online, or reachable. WMI has a class called Win32_PingStatus which sends a signal to the server to see if it's responding, and if so, returns a 0 in the StatusCode property of one of the result objects.

The first thing we need to do is to define in a file the list of SQL Servers we're interested in. There are most likely very many instances of SQL Server on your network that you don't care about. (Lots of applications using SQL Server Express edition, for example.) We'll put the servers we do care about in a file called servers.txt, and it looks like this.


PowerShell provides a cmdlet called Get-Content which will read this file and place its contents in a collection we'll iterate through to return our server information. Once we load the collection we'll use a ForEach-Object cmdlet to loop through the servers. For each server we'll extract the machine name by using the Split method on the server name and then returning the first part. We'll use that to ping the server to see if it's responding. If it is, then we can connect using SMO to return the server information we're interested in.

Here's the script:

#Pings a list of servers contained in the text file servers.txt and if
#the server responds, returns SQL Server information from each server
# Change log:
# February 1, 2009: Allen White
# Initial Version

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$servers = Get-Content 'servers.txt'

ForEach ($server in $servers) {
$nm = $server.Split("\")
$machine = $nm[0]
# Ping the machine to see if it's on the network
$results = Get-WMIObject -query "select StatusCode
from Win32_PingStatus where Address = '$machine'"
$responds = $false
ForEach ($result in $results) {
# If the machine responds break out of the result loop and indicate success
if ($result.statuscode -eq 0) {
$responds = $true

If ($responds) {
# Gather info from the server because it responds
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server
$s.Information | select $s.Name, Version, Edition | format-table -Autosize
} else {
# Let the user know we couldn't connect to the server
Write-Output "$server does not respond"

It should be easy for you to modify this to add information you find useful.


Published Sunday, February 1, 2009 5:22 PM by AllenMWhite



Pradeep Adiga said:

Very useful snip. Please share more information about how to monitor sql servers using PS.

February 1, 2009 11:19 PM

Buck Woody said:

As always - awesome!

February 2, 2009 1:32 PM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement