Skip to content

Instantly share code, notes, and snippets.

Embed
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]
@rvrsh3ll

This comment has been minimized.

Copy link

rvrsh3ll commented Sep 10, 2015

nice work, handy for my penetration tests!

@lingfei87

This comment has been minimized.

Copy link

lingfei87 commented Oct 20, 2016

Should not be given username and password for DB?

@mramrali

This comment has been minimized.

Copy link

mramrali commented Apr 1, 2017

How to path UserName and password to the function.

@itepe

This comment has been minimized.

Copy link

itepe commented Jul 2, 2017

Nice! Works fine but only on my local SQL Server. How to use your script to remote database with user/password authentication?
Many thanks!

@JTravis76

This comment has been minimized.

Copy link

JTravis76 commented Jul 10, 2017

Great Script!!
For those who would like, here is a way to use a connection string to connect to remote SQL server.

$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$conn.ConnectionString = "Data Source=ddsqldev02;Initial Catalog=MyDB;User ID=SQLUSER;Password=SQLPASSWORD;MultipleActiveResultSets=True;Application Name=Powershell"

GenerateDBScript $conn "MyDB" "C:\Users\Public\Export"
Since the first argument is a [string]$serverName , will need to remove [string] or change to:
function GenerateDBScript([Microsoft.SqlServer.Management.Common.ServerConnection]$serverName, [string]$dbname, [string]$scriptpath)

Other connection option found here.
https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection.aspx

@aadhiran

This comment has been minimized.

Copy link

aadhiran commented Mar 18, 2019

Thanks for the post but it takes ever for a larger database (size: ~270 MB) any help to improve the time?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.