Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Export MSSQL schema with PowerShell. This script will export your schema definitions for tables, stored procs, triggers, functions and views to .sql files
# Usage: powershell ExportSchema.ps1 "SERVERNAME" "DATABASE" "C:\<YourOutputPath>"
# Start Script
Set-ExecutionPolicy RemoteSigned
# Set-ExecutionPolicy -ExecutionPolicy:Unrestricted -Scope:LocalMachine
function GenerateDBScript([string]$serverName, [string]$dbname, [string]$scriptpath)
{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
$srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
$db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
$db = $srv.Databases[$dbname]
$scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
$scr.Server = $srv
$options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$options.AllowSystemObjects = $false
$options.IncludeDatabaseContext = $true
$options.IncludeIfNotExists = $false
$options.ClusteredIndexes = $true
$options.Default = $true
$options.DriAll = $true
$options.Indexes = $true
$options.NonClusteredIndexes = $true
$options.IncludeHeaders = $false
$options.ToFileOnly = $true
$options.AppendToFile = $true
$options.ScriptDrops = $false
# Set options for SMO.Scripter
$scr.Options = $options
#=============
# Tables
#=============
$options.FileName = $scriptpath + "\$($dbname)_tables.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($tb in $db.Tables)
{
If ($tb.IsSystemObject -eq $FALSE)
{
$smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection
$smoObjects.Add($tb.Urn)
$scr.Script($smoObjects)
}
}
#=============
# Views
#=============
$options.FileName = $scriptpath + "\$($dbname)_views.sql"
New-Item $options.FileName -type file -force | Out-Null
$views = $db.Views | where {$_.IsSystemObject -eq $false}
Foreach ($view in $views)
{
if ($views -ne $null)
{
$scr.Script($view)
}
}
#=============
# StoredProcedures
#=============
$StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_stored_procs.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($StoredProcedure in $StoredProcedures)
{
if ($StoredProcedures -ne $null)
{
$scr.Script($StoredProcedure)
}
}
#=============
# Functions
#=============
$UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
$options.FileName = $scriptpath + "\$($dbname)_functions.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($function in $UserDefinedFunctions)
{
if ($UserDefinedFunctions -ne $null)
{
$scr.Script($function)
}
}
#=============
# DBTriggers
#=============
$DBTriggers = $db.Triggers
$options.FileName = $scriptpath + "\$($dbname)_db_triggers.sql"
New-Item $options.FileName -type file -force | Out-Null
foreach ($trigger in $db.triggers)
{
if ($DBTriggers -ne $null)
{
$scr.Script($DBTriggers)
}
}
#=============
# Table Triggers
#=============
$options.FileName = $scriptpath + "\$($dbname)_table_triggers.sql"
New-Item $options.FileName -type file -force | Out-Null
Foreach ($tb in $db.Tables)
{
if($tb.triggers -ne $null)
{
foreach ($trigger in $tb.triggers)
{
$scr.Script($trigger)
}
}
}
}
#=============
# Execute
#=============
GenerateDBScript $args[0] $args[1] $args[2]

nice work, handy for my penetration tests!

Should not be given username and password for DB?

mramrali commented Apr 1, 2017

How to path UserName and password to the function.

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