SMO and PowerShell 1.0

This is an introductory post, in a series of posts, where I intend to use PowerShell and SMO to describe a database schema versioning process that supports the needs of your Agile development team. I'm not going to bore anyone with a Powershell or SMO Primer, so if you have any questions please contact me at niklas@hemdal.com for assistance.

For this post, I just wanted to expose a little PowerShell script to demonstrate how amazingly powerful this technology is. The following code enumerates the schema objects for a user-supplied Microsoft SQL Server database, and generates the corresponding T-SQL create script files for objects whose names match a user-supplied regular expression. I chose to enumerate the objects based upon the object name, but I have left script comments in to specify the enumeration using the schema object type (i.e. Stored Procedure, Table, Foreign Key, etc.). To create T-SQL create script files for all database schema objects simply supply ".*" as the regular expression to match.

param (
[string] $serverName,
[string] $dbName,
[string] $objectPattern,
[string] $outputPath
)

function ScriptSqlObject([String]$obj,[string]$objType,[string]$targetPath)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$server = new-object ( 'Microsoft.SqlServer.Management.Smo.Server')$serverName
$db = $server.Databases[$dbName]

$o = new-object ( 'Microsoft.SqlServer.Management.Smo.Scripter') ($server)
$o.Options.WithDependencies = $false

switch ( $objType.Trim() )
{
"U" { $actualObject = $db.Tables[$obj]; $extension = ".tbl"; break; }
"P" { $actualObject = $db.StoredProcedures[$obj]; $extension = ".sp"; break; }
Default { return; }
}

$f = [System.IO.Path]::Combine($targetPath, $actualObject.Name + $extension)
if ( [System.IO.File]::Exists($f) -eq $true )
{
[System.IO.File]::Delete($f)
}
$o.Options.FileName = $f
$o.Options.AppendToFile = $true
$o.Options.ScriptDrops = $true
$o.Options.IncludeIfNotExists = $true
# script the drop
$o.Script($actualObject.Urn)

$o.Options.DriPrimaryKey = $true
$o.Options.ScriptDrops = $false
$o.Options.IncludeIfNotExists = $false
# script the create
$o.Script($actualObject.Urn)
}

$cn = new-object System.Data.SqlClient.SqlConnection
$cn.ConnectionString = "Server=$serverName;Database=$dbName;Integrated
Security=True"
$cmd = new-object System.Data.SqlClient.SqlCommand $cmd.CommandText = "SELECT * FROM sys.objects"
$cmd.Connection = $cn
$a = new-object System.Data.SqlClient.SqlDataAdapter
$a.SelectCommand = $cmd
$ds = new-object System.Data.DataSet
$a.Fill($ds)
$cn.Close()
$names = @{}
$ds.Tables[0] | %{if($_.Name -match [regex]$objectPattern) { $names[$_.Name] = $_.Type } }
# to enumerate all table objects specify “U\s” as input to $objectPattern
#$ds.Tables[0] | %{if($_.Type -match [regex]$objectPattern) { $names[$_.Name] = $_.Type } }
foreach ( $key in $names.Keys )
{
ScriptSqlObject $element $names[$key] $outputPath
}

#$sr=new-object System.IO.StreamReader("C:\")
#$script=sr.ReadToEnd
#$db.ExecuteNonQuery($script)

Posted at 07:42 PM  Permalink