Generate a file-per-object representation of a given SQL Server database using PowerShell and SMO.

GenerateSqlScripts.ps1

# Adapted from http://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/

<#
.SYNOPSIS
Generate file-per-object scripts of specified server and database.

.DESCRIPTION
Generate file-per-object scripts of specified server and database to specified directory. Attempts to create specified directory if not found.

.PARAMETER ServerName
Specifies the database server hostname.

.PARAMETER Database
Specifies the name of the database you want to script as objects to files.

.PARAMETER SavePath
Specifies the directory where you want to store the generated scripts.
#>

[CmdletBinding()]
Param(
    [Parameter(Mandatory = $true, Position = 1)]
        [string]$ServerName,
    [Parameter(Mandatory = $true, Position = 2)]
        [string]$Database,
    [Parameter(Mandatory = $true, Position = 3)]
        [string]$SavePath
)

# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended library
$v = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9')
{
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
}
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
Set-PSDebug -strict # catch a few extra bugs

$ErrorActionPreference = "continue"

$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $ServerName #attach to the server
if ($srv.ServerType -eq $null) # check to see if we found a server
{
    throw "Unable to connect to server '$ServerName' "
}

# Loop through the databases and do a case insensitive search for .Name
# Doing this instead of $srv.Databases[$Database] because it can change the character case
# of the .Name property (i.e. 'ENTERPRISE' becomes 'enterprise') of the located Database object.
$db = $null # initialize empty variable
foreach ($finddb in $srv.Databases)
{
    if ($finddb.Name -eq $Database)
    {
        $db = $finddb
        break
    }
}

if ($db -eq $null)
{
    throw "Unable to find database '$Database'"
}


# create scripter object (used by the function ScriptOutDbObj())
$scripter = New-Object ("Microsoft.SqlServer.Management.Smo.Scripter") $srv #create the scripter

$scripter.Options.AllowSystemObjects = $false
$scripter.Options.AnsiFile = $true
$scripter.Options.AnsiPadding = $true # true = SET ANSI_PADDING statements
$scripter.Options.Default = $true
$scripter.Options.DriAll = $true
$scripter.Options.Encoding = New-Object ("System.Text.ASCIIEncoding")
$scripter.Options.ExtendedProperties = $true
$scripter.Options.IncludeDatabaseContext = $true # true = USE <databasename> statements
$scripter.Options.IncludeHeaders = $false
$scripter.Options.Indexes = $true
$scripter.Options.NoCollation = $true # true = don't script verbose collation info in table scripts
$scripter.Options.SchemaQualify = $true
$scripter.Options.ScriptDrops = $false
$scripter.Options.ToFileOnly = $true
$scripter.Options.Triggers = $true
$scripter.Options.WithDependencies = $false

# create the directory if necessary (SMO doesn't).
if (!(Test-Path -Path $SavePath))
{
    Write-Verbose "Creating directory at '$SavePath'"

    try
    {
        New-Item $SavePath -Type Directory | Out-Null
    }
    catch [System.Exception]
    {
        throw "Error while creating '$SavePath'"
    }
}

################################################################################
# Function Script Out Database Object
#
# Function that accepts a Smo.Database-related object or an object with a valid
# Urn property, then calls the already instantiated Scripter object to write
# sql script to file.
#
# Args: $inObj =     Database object from either a "list" (generated by EnumObjects
#                    method) or a database object with a Urn property (i.e. Table,
#                    StoredProcedure, etc)
################################################################################
Function ScriptOutDbObj($inObj)
{
    # Create a single element URN array
    $UrnCollection = New-Object ("Microsoft.SqlServer.Management.Smo.UrnCollection")
    $UrnCollection.Add($inObj.Urn)

    # get the valid Urn.Type string for the file name
    $typeName = $UrnCollection.Item(0).Type

    # tell the scripter object where to write it
    $SavePath = Resolve-Path $SavePath # get the full path of passed in argument (for scripter object's benefit)
    $scripter.Options.Filename = Join-Path $SavePath "$($inObj.Schema + '.' -Replace '^\.','')$($inObj.Name -Replace '[\\\/\:]',' ').$typeName.sql"

    # a bit of progress reporting...
    Write-Verbose $scripter.Options.FileName

    #and write out the object to the specified file
    $scripter.Script($UrnCollection)
}
# End of Function ScriptOutDbObj
################################################################################

## MAIN PROGRAM LOGIC ##

# now get all the object types except extended stored procedures and a few others we don't want
# by creating a "bitmap" of the DatabaseObjectTypes enum:
$objectTypeFlags = [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::All -bxor (
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::Certificate +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::DatabaseRole +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ExtendedStoredProcedure +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::MessageType +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceBroker +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceContract +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceQueue +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ServiceRoute +
        [long][Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::SqlAssembly)

#and we store them in a datatable
$d = New-Object ("System.Data.DataTable")

#get everything except the information schema, system views, and some other extra items
$d = $db.EnumObjects($objectTypeFlags) |
    Where-Object {
        $_.Schema -ne "sys" -and
        $_.Name -ne "sys" -and
        $_.Schema -ne "information_schema" -and
        $_.Name -ne "information_schema" -and
        $_.Schema -notlike "db_*" -and
        $_.Name -notlike "db_*" -and
        $_.Name -notlike "sp_*diagram*" -and
        $_.Name -ne "fn_diagramobjects" -and
        $_.Name -ne "sysdiagrams" -and
        $_.Schema -ne "guest" -and
        $_.Name -ne "guest" }


#and write out each scriptable object as a file in the directory you specify
$d | ForEach-Object {
    #for every object we have in the datatable.
    ScriptOutDbObj $_
}

# Next, script out Database Triggers (DatabaseDdlTrigger) separately because they are not returned by Database.EnumObjects()
foreach ($t in $db.Triggers)
{
    ScriptOutDbObj $t
}

# also script out the database definition itself
ScriptOutDbObj $db

Write-Verbose "Done!"

If you are interested, I also took this function and modified it. It's part of this module: tcdbtools

The help for it: Invoke-DBScriptObjects.md