Capturing SQL Generated by SMO in Powershell

  • Article

The following example uses the get-sqlserver.ps1 script in my search path.

get-sqlserver.ps1:

 param ($server=".")  
$null = [reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo") 
new-object Microsoft.SqlServer.Management.Smo.Server $server;

This example will shows how to use SMO objects to generate a SQL Script that will drop all the rowguid columns from the adventure works database:

 PS C:\demo> $srv = get-sqlserver  
PS C:\demo> $db = $srv.Databases["adventureworks"] 
# Find the tables that have a rowguid column 
PS C:\demo> foreach ($tbl in $db.tables) {$tbl.columns | %{ if($_.name -eq "rowguid") {$tbl.name}}} 
Employee 
EmployeeAddress 
Address 
AddressType 
Contact 
StateProvince 
Product 
... 

# set SMO connection to capture mode 
PS C:\demo> $srv.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::capturesql 

# drop the row guid column & alter the table  
#  SMO is set to capture only so the database is not updated 
PS C:\demo> foreach ($tbl in $db.tables) {$tbl.columns | %{ if($_.name -eq "rowguid") {$_.drop();$tbl.alter();}}} 

# Check the captured sql
PS C:\demo> $srv.ConnectionContext.CapturedSql.Text 
USE [adventureworks] 
ALTER TABLE [HumanResources].[Employee] DROP CONSTRAINT [DF_Employee_rowguid] 
ALTER TABLE [HumanResources].[Employee] DROP COLUMN [rowguid] 
... 

# save the sql to a file 
PS C:\demo> $srv.ConnectionContext.CapturedSql.Text | sc update.sql