Create a gist now

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.

Show comment
Hide comment
@rvrsh3ll

rvrsh3ll Sep 10, 2015

nice work, handy for my penetration tests!

rvrsh3ll commented Sep 10, 2015

nice work, handy for my penetration tests!

@lingfei87

This comment has been minimized.

Show comment
Hide comment
@lingfei87

lingfei87 Oct 20, 2016

Should not be given username and password for DB?

lingfei87 commented Oct 20, 2016

Should not be given username and password for DB?

@mramrali

This comment has been minimized.

Show comment
Hide comment
@mramrali

mramrali Apr 1, 2017

How to path UserName and password to the function.

mramrali commented Apr 1, 2017

How to path UserName and password to the function.

@itepe

This comment has been minimized.

Show comment
Hide comment
@itepe

itepe 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!

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.

Show comment
Hide comment
@JTravis76

JTravis76 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

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

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