Combining a COM object to a simple solution to get SQL Server Information

This is a simple solution that will generate a *.txt report with some SQL Server information. The “Scripting.FileSystem” COM Object is use to verify that the file is deleted before is created by the Powershell “Out-File” CMDlet. We’ll use the COM object methods *.FileExist() and *.DeleteFile().

After loading the SMO assembly you gather the database information, then using the “foreach” CMDlet get the information for each servername from the string array $SQLLocalList.

Requirements:

1. You need to have installed both SQL Server 2005 SMO and SQL Server 2008 on your computer to be able to manage all SQL Servers (2000/2005/2008) on the network.

2. This script will work with both Powershell version 1.0 and CTP version 2.0.

3. Have the proper permission to access your SQL Servers and Powershell script.

 

###################################################

## SQLLocalInfo.ps1

## Author: Max Trinidad, 01/21/2008

###################################################

#–Set execution policy to allow to run this script

Set-executionpolicy -executionpolicy remotesigned

 

#–Load the SQL Server SMO assembly

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

 

#–Build the array with the SQL Server names

$SQLLocallist = "SQLSvr2k","SQLSvr2k5”, “SQLSql2k8”;

 

#–Build the string holding the location of the output file

$Filename = "C:\Developer\Max Events 2008\SQLServerInfo.txt";

#–Build the COM object holding the collection of methods we can use

$FileSystem = new-object -com "Scripting.FileSystemObject";

 

#–If the file already exist then delete

if ($FileSystem.FileExists($FileName)) {  

  $FileSystem.DeleteFile($FileName) ; }

 

#–Basically for each server name in the Array then get the information and append to the output file

foreach ($srvr in $SQLLocallist)

{

  #–Build the variable with the SQL Server collection for each server and then create the outfile

  $MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $Srvr

  Out-File -filepath $Filename -inputobject ($MySQL.Information | Select Parent, Product, Edition, VersionString) -append

  Out-File -filepath $Filename -inputobject ($MySQL.Databases | Select Name, Parent) –append

}

 

##–Display the outfile in Notepad

ii $FileName;

######## END OF SCRIPT CODE #########

Sample Report Ouput

Use the “Get-member” in order to get more properties values to use in the “Select”.  Have fun!

Advertisements

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in PowerShell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s