Month: February 2009

Scan network for instances of MS SQL with Powershell

PROBLEM: You need to find out which computers and servers in my network have an instance of MS SQL installed.

SOLUTION: In this solution we’ll create an instance of the Windows Management Instrumentation (WMI) for each computer in a list. We’ll then query the Win32_Service class for the existence of a service which name starts with “MSSQL”. Go ahead and open a Notepad session and enter the name of several computes you manage in your network, one name per line, and save it with the file name isSQLInstalled.txt. I saved mine in C: for simplicity.

foreach($svr in get-content "C:isSQLInstalled.txt") {
    $filter = "name LIKE 'MSSQL%'"
    $result = get-wmiobject win32_Service -f $filter -computername $svr
    $resulttxt = [bool] $result
    write-host "$svr`: $resulttxt"
}

You need to be member of the Administrator role in each computer in the list for this script to execute successfully. This is a very basic way to search for instances of MS SQL installed in your network. One way you can expand this solution is by using a variable to capture the computer names in your network via Active Directory. You can then use this variable in the loop instead of reading the computer names from a file as we did in this solution.

How to Script Database Tables with PowerShell

PROBLEM: I need an easy way to script out all the tables from a database in MS SQL 2000 and above.

SOLUTION: Like anything in programming, there are a thousand ways to skin a cat. Here, I’ll show you the simplest way I found to meet this request.

Go ahead and open a new session of PowerShell. You can type or copy and paste in your PS session window all the code in this solution. We will start by loading the SMO assembly.

[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")|out-null

Next, we’ll create an instance of SMO server object, database object, and scripter object

$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" "."
$db = new-object("Microsoft.SqlServer.Management.SMO.Database")
$scr = new-object("Microsoft.SqlServer.Management.SMO.Scripter")

We then assign the instance of MS SQL Server we want the scripter to use. We wrap it up by calling the Script() method with an SQL SMO collection of objects to be script out –in this case, all tables. 

$scr.Server = $srv
$scr.Script($srv.Databases["AdventureWorks"].tables)

That’s it! The simplest way to script out all tables from a database with PowerShell. In a future article, I’ll expand from this solution by writing a script that will take parameters, use SMO Scripter Options, filter tables to be script out, and then some.