Export MSSQL schema with PowerShell. This script will export your schema definitions for tables, stored procs, triggers, functions and views to .sql files

ExportSchema.ps1

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

How to path UserName and password to the function.

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

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

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

mistake in line: $scr.Script($trigger)
#============= # 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($trigger) # <-- } }

Hi, I used your script as a template and made it hopefully a bit more comfortable: https://github.com/binbash23/mssql_generate_schema_scripts

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

you can check the rebuild of the script, which makes the checks if an object is a system object more performant: https://github.com/binbash23/mssql_generate_schema_scripts