Month: November 2017

@brianszadek says: I am looking to automate the scripting of objects with PowerShell for easier source control integration.

I thought this would be a little bit easier, but it looks like the SMO way of doing this is still fairly complicated. Maybe something I can look at simplifying for the project in a bit 🙂

For completeness I also included my favorite tool (schemazen) and Microsoft’s new scripting tool if you want a quick shell approach.

PowerShell: (dbatools)

$SqlInstance = "192.168.0.23,1433"
$DatabaseName = "breakme"
$Server = Connect-DbaInstance $SqlInstance
$Database = $Server.Databases[$DatabaseName]

# Define an empty list
$ObjectList = @()
$Options = New-DbaScriptingOption
$Options.AllowSystemObjects =
  $Options.AppendToFile = $false

$Options.Triggers =
  $Options.ToFileOnly =
  $Options.ScriptBatchTerminator =
  $Options.DriAll =
  $Options.Indexes =
  $Options.Default =
  $Options.SchemaQualify =
  $Options.ClusteredIndexes =
  $Options.NonClusteredIndexes = $true

# Surprisingly, $Database.EnumObjects will return a set of strings, not SMO objects, this is kind of sucky.
$ObjectList += foreach ($ob in $Database.EnumObjects()){ # Get all objects
  if ($ob.Schema -notin "sys","INFORMATION_SCHEMA"){ # Not in system schemas
    $o = $Database.Resolve($ob.Urn) # Resolve the object path to an SMO object
    if (($o | Get-Member | Select-Object -exp Name) -contains "Script"){ # If it has a script property
      $o # Return it
    }
  }
}


function Rename-ObjectToSqlFile {
  param ($ObjectName)
  $Name =  $ObjectName -match '.*\/(.*)'
  if ($Name -eq $false){
    $Name = $ObjectName
  } else {
    $Name = $matches[1]
  }
  $Name = $Name + ".sql"
  return $Name
}

# Iterate, get one file per object, decide on how you want to name them, because things like event notifications are uris
foreach ($obj in $ObjectList) {
  $Name = Rename-ObjectToSqlFile -ObjectName $obj.name
  Export-DbaScript $obj -ScriptingOptionsObject $Options -Path $Name # Export it with the dbatools powers
}
# Or export based on category
Export-DbaScript $ObjectList -ScriptingOptionsObject $Options # Export object categories to each file

Note: Export-DbaScript currently has a bug that prevents servers using SQL Authentication from scripting, this has been fixed in the next version.

Command line executables

Schema Zen (C#) (Free, OSS)

If you haven’t heard of SchemaZen, its a great and insanely fast tool for exporting all the objects in a database to a folder, and if needed, take the same folder and create a database from it.

There are also some experimental data export features.

MSSQL Scripter (python) (Free, OSS)

If you haven’t used the MSSSQL Scripter, its Microsoft’s tool that utilizes the new SqlToolsService to request and script out DDL information. They have a lovely architecture page if you want to learn more.