Generates schema and data for a given SQL Server table to file.

script-table.ps1

<#
  Generates a CREATE/INSERT script for a given SQL Server db table (e.g. Localizations)
  Peter Tyrrell, Andornot, www.andornot.com
  
  sqlps dependency
  If module sqlps does not exist, install from:
    Microsoft SQL Server 2016 Feature Pack (https://www.microsoft.com/en-us/download/details.aspx?id=52676)
    - SQLSysClrTypes.msi
    - SharedManagementObjects.msi
    - PowershellTools.msi
#>

param (
  [string]$server = "DATAZOR",
  [string]$database = "andi",
  [string]$table = "Localizations",
  [string]$out = "c:\3_localization-table-with-data.sql"
)

Push-Location
Import-Module "sqlps" -DisableNameChecking
Pop-Location

<#
  MAIN
#>

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $server
$srv.ConnectionContext.StatementTimeout = 0

# test server instance is available
try {
  $srv.databases | Out-Null
}
catch {
  write-host "SQL Server $server not available." -ForegroundColor Red
  exit 1
}

$db = $srv.databases[$database]

# test table is available
try {
   $db.Tables[$table] | Out-Null
}
catch {
  write-host "$database.$table table not available." -ForegroundColor Red
  exit 1
}

# generate script
write-host "Generating script..."
Push-Location
$scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter $srv
$scripter.Options.IncludeIfNotExists = $true
$scripter.Options.ScriptSchema = $true
$scripter.Options.ScriptData = $true
$scripter.Options.IncludeHeaders = $true
$scripter.Options.ToFileOnly = $true
$scripter.Options.FileName = $out
$scripter.EnumScript(@($db.Tables[$table]))
Pop-Location

exit 0