Skip to content

Instantly share code, notes, and snippets.

@mcbos
Forked from badmotorfinger/psmo.ps1
Last active November 27, 2019 17:50
Show Gist options
  • Save mcbos/fac0d11f6b73550db4f465c2761c3a8a to your computer and use it in GitHub Desktop.
Save mcbos/fac0d11f6b73550db4f465c2761c3a8a to your computer and use it in GitHub Desktop.
Generates scripts for most SQL Server database objects using PowerShell (SMO objects)
################################################################################################################################
#
# Script Name : SmoDb
# Version : 1.0
# Author : Vince Panuccio
# Purpose :
# This script generates one SQL script per database object including Stored Procedures, Tables, Views,
# User Defined Functions and User Defined Table Types. Useful for versionining a database in a VCS.
#
# Usage :
# Set variables at the top of the script then execute.
#
# Note :
# Only tested on SQL Server 2008r2
#
################################################################################################################################
param (
[string]$server = "localhost",
[string]$database = "NerdDinner",
[string]$output_path = "C:\dev\nerddinner\Schema"
)
$schema = "dbo"
$table_path = "$output_path\Table\"
$storedProcs_path = "$output_path\StoredProcedure\"
$triggers_path = "$output_path\Triggers\"
$views_path = "$output_path\View\"
$udfs_path = "$output_path\UserDefinedFunction\"
$textCatalog_path = "$output_path\FullTextCatalog\"
$udtts_path = "$output_path\UserDefinedTableTypes\"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$cred = Get-Credential
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.set_Login($cred.username)
$srv.ConnectionContext.set_SecurePassword($cred.password)
$srv.ConnectionContext.Connect()
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database")
$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table")
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($srv)
# Get the database and table objects
$db = $srv.Databases[$database]
$tbl = $db.tables | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }
$storedProcs = $db.StoredProcedures | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }
$triggers = $db.Triggers + ($tbl | % { $_.Triggers })
$views = $db.Views | Where-object { $_.schema -eq $schema }
$udfs = $db.UserDefinedFunctions | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject }
$catlog = $db.FullTextCatalogs
$udtts = $db.UserDefinedTableTypes | Where-object { $_.schema -eq $schema }
# Set scripter options to ensure only data is scripted
$scripter.Options.ScriptSchema = $true;
$scripter.Options.ScriptData = $false;
#Exclude GOs after every line
$scripter.Options.NoCommandTerminator = $false;
$scripter.Options.ToFileOnly = $true
$scripter.Options.AllowSystemObjects = $false
$scripter.Options.Permissions = $true
$scripter.Options.DriAllConstraints = $true
$scripter.Options.SchemaQualify = $true
$scripter.Options.AnsiFile = $true
$scripter.Options.SchemaQualifyForeignKeysReferences = $true
$scripter.Options.Indexes = $true
$scripter.Options.DriIndexes = $true
$scripter.Options.DriClustered = $true
$scripter.Options.DriNonClustered = $true
$scripter.Options.NonClusteredIndexes = $true
$scripter.Options.ClusteredIndexes = $true
$scripter.Options.FullTextIndexes = $true
$scripter.Options.EnforceScriptingOptions = $true
function CopyObjectsToFiles($objects, $outDir) {
if (-not (Test-Path $outDir)) {
[System.IO.Directory]::CreateDirectory($outDir)
}
foreach ($o in $objects) {
if ($o -ne $null) {
$schemaPrefix = ""
if ($o.Schema -ne $null -and $o.Schema -ne "") {
$schemaPrefix = $o.Schema + "."
}
$scripter.Options.FileName = $outDir + $schemaPrefix + $o.Name + ".sql"
Write-Host "Writing " $scripter.Options.FileName
$scripter.EnumScript($o)
}
}
}
# Output the scripts
CopyObjectsToFiles $tbl $table_path
CopyObjectsToFiles $storedProcs $storedProcs_path
CopyObjectsToFiles $triggers $triggers_path
CopyObjectsToFiles $views $views_path
CopyObjectsToFiles $catlog $textCatalog_path
CopyObjectsToFiles $udtts $udtts_path
CopyObjectsToFiles $udfs $udfs_path
Write-Host "Finished at" (Get-Date)
@mcbos
Copy link
Author

mcbos commented Oct 30, 2019

Parameterized connection information, added credentials

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment