Powershell and SMO scripting – Part 1

Published 13 August 09 09:18 PM | MartinBell

One of the great things you can do with Powershell is that you can use the classes from SQL Server Management Object (SMO). I’ve only found 3 examples of using Powershell and SMO in Books Online, one scripts all views, one creates a database and one stops and starts the service with WMI ( which could be very handy Smile ).

Let's look at the scripting example from BOL:

Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }


All you need to do to run this script is to copy the code, then right click on (say) the server name in object explorer and choose the Start Powershell menu option. Then paste the code into the window by clicking the icon in the top right of the windows and choosing edit and then paste.

If you have never run this before you will probably get the error:


Remove-Item : Cannot find path 'C:\PowerShell\CreateViews.sql' because it does not exist.
At line:1 char:12
+ Remove-Item  <<<< C:\PowerShell\CreateViews.sql

You can suppress this by piping the output of the command to the Out-Null cmdlet i.e.

Remove-Item C:\PowerShell\CreateViews.sql | Out-Null

Remove-Item also has many built-in aliases which include "del", "erase", "rmdir", "rd", "ri", or "rm", so you could use:

del C:\PowerShell\CreateViews.sql | Out-Null

and even through it is a file you can use rmdir:

rmdir C:\PowerShell\CreateViews.sql | Out-Null

The next line:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views

Changes the location to be the views folder in the AdventureWorks database, which is on the default instance of the localhost. cd is an alias you can use instead of typing out Set-Location.

Finally the commands that do all the output is

foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }

In my previous Powershell article I showed how to use the foreach to process files in a directory on disc, the power of PowerShell allows you to do this for views in a given database.

Using the SMO Script() method the create statements for each view is then appended to the C:\PowerShell\CreateViews.sql  file specified as a parameter to the Out-File cmdlet.

If you look at the CreateViews,sql you will see CREATE VIEW statements for each view in the database separated by the set statements

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

This is all pretty powerful stuff and very easy to do, but what if I wanted to output each view definition into it’s own file? I did something similar in my previous Powershell article so I could easily change the script to be:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views foreach ($Item in Get-ChildItem) {
$out = "C:\PowerShell\" + $Item.Name + ".sql"
$Item.Script() | Out-File -Filepath $out
}

This script will create a single file named after the view and containing the CREATE statement for that view. SQL Server 2008 Management Studio has a great option on the Object Explore that allows you to script both a drop and create statement for an object. This is wonderful for scripting objects to store in a source code control system, but doing each one individually in Object explorer is not a task you really want to do. It’s also unfortunate that the scripting wizard does not allow you to do this. So I looked around to see how I could and found the SMO Scripter class and the Options Property. This property is a ScriptingOptions type with a hosts of members which includes the ScriptDrops property. So here is the script I came up with:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;

foreach ($Item in Get-ChildItem) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Name + ".sql"
$Scripter.Script($Item)
}

Unfortunately this only scripted statements like:

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[Person].[vAdditionalContactInfo]'))
DROP VIEW [Person].[vAdditionalContactInfo]
GO

i.e. only drop statements, and what I wanted was a combination of both of my scripts i.e. if exists drop the view then create it. So here is what I ended up with:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}

In my next article I will look at scripting other objects.

Filed under: , ,

Comments

# Dew Drop – August 14, 2009 | Alvin Ashcraft's Morning Dew said on August 14, 2009 02:08 PM:

Pingback from  Dew Drop &#8211; August 14, 2009 | Alvin Ashcraft's Morning Dew

# Martin Bell UK SQL Server MVP said on January 1, 2010 03:31 PM:

How much use is Powershell to a DBA when Windows Authentication is not available?

This Blog

SQL Blogs

Syndication