Sql-server – SMO v11 (SQL Server 2012) not scripting key definitions, indexes, and constraints

I have a .ps1 PowerShell script that runs on a Central Management Server. The script goes out to all databases in our environment, and scripts out all of of objects. For tables scripts, the output files have always included the table definition along with constraints, primary keys, and indexes.

We recently upgraded our CMS to Powershell 3.0 and SQL Server 2012 and now our output scripts have table definition only. The indexes, constraints, and key definitions are no longer part of the script. Does anyone know what has changed with SMO in this SQL Server 2012 release and how I can resolve my issue.

My code is below.

# LoopInstances

$InstanceListFile = "C:\temp\serverlist.txt"
$CurrentDay = Get-Date -Format dd
$InstanceList = Get-Content $InstanceListFile

foreach ($InstanceName in $InstanceList)
{
    if (($InstanceName -ne 'Server1') -and ($InstanceName -ne 'Server2'))
    {
    # Configuration
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended')  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | Out-Null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO") | Out-Null

    $MyScripter = New-Object "Microsoft.SqlServer.Management.SMO.Scripter"

    $InstanceObject = New-Object "Microsoft.SqlServer.Management.SMO.Server" "$InstanceName"

    $RepInstanceObject = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" "$InstanceName"

    # The SetDefaultInitFields method tells the SMO API to add the IsSystemObject property to the default list for this session

    $InstanceObject.SetDefaultInitFields("Microsoft.SqlServer.Management.SMO.ExtendedStoredProcedure", "IsSystemObject")
    $InstanceObject.SetDefaultInitFields("Microsoft.SqlServer.Management.SMO.StoredProcedure", "IsSystemObject")
    $InstanceObject.SetDefaultInitFields("Microsoft.SqlServer.Management.SMO.Table", "IsSystemObject")
    $InstanceObject.SetDefaultInitFields("Microsoft.SqlServer.Management.SMO.View", "IsSystemObject")
    $InstanceObject.SetDefaultInitFields("Microsoft.SqlServer.Management.SMO.UserDefinedFunction", "IsSystemObject")
    $MyScripter.Server = $InstanceObject
    $ScripterOptions = $MyScripter.Options

    # SetScriptOptions

    $ScripterOptions.ToFileOnly = $True
    $ScripterOptions.AppendToFile = $True
    $ScripterOptions.ContinueScriptingOnError = $True
    $ScripterOptions.LoginSid = $True
    $ScripterOptions.AgentAlertJob = $True
    $ScripterOptions.AgentNotify = $True
    $ScripterOptions.Bindings = $True
    $ScripterOptions.DriAll = $True
    $ScripterOptions.Indexes = $True
    $ScripterOptions.Permissions = $True
    $ScripterOptions.SchemaQualify = $True
    $ScripterOptions.Triggers = $True
    $ScripterOptions.IncludeIfNotExists = $True
    $ScripterOptions.ExtendedProperties = $True

    # SetSchemaPath


    $SchemaPath = "C:\Temp\" + $CurrentDay + "\" + $InstanceName
    if ((Test-Path $SchemaPath) -ne $True) {mkdir $SchemaPath | Out-Null} 

    # DisplayInstance

    Write-Host "------------------------------------------------------------"
    Write-Host $InstanceName - (Get-Date)
    Write-Host "------------------------------------------------------------"

    # CheckVersion

    if ($InstanceObject.Information.VersionString.substring(0,2) -eq 10)
    {

        $InstanceObjectList = "Audits","BackupDevices","Credentials","CryptographicProviders","Databases","EndPoints","LinkedServers","Logins","ServerAuditSpecifications","Triggers","UserDefinedMessages"
        $JobServerObjectList = "AlertCategories","Alerts","JobCategories","Jobs","OperatorCategories","Operators","ProxyAccounts","SharedSchedules","TargetServerGroups","TargetServers"
        $DatabaseObjectList = "Assemblies","AsymmetricKeys","Certificates","DatabaseAuditSpecifications","Defaults","ExtendedProperties","FullTextCatalogs","FullTextStopLists","PartitionFunctions","PartitionSchemes","PlanGuides","Roles","Rules","Schemas","Synonyms","Triggers","UserDefinedAggregates","UserDefinedDataTypes","UserDefinedTableTypes","UserDefinedTypes","Users","XmlSchemaCollections"
    }

    if ($InstanceObject.Information.VersionString.substring(0,1) -eq 9)
    {
        $InstanceObjectList = "BackupDevices","Credentials","Databases","EndPoints","LinkedServers","Logins","Triggers","UserDefinedMessages"
        $JobServerObjectList = "AlertCategories","Alerts","JobCategories","Jobs","OperatorCategories","Operators","ProxyAccounts","SharedSchedules","TargetServerGroups","TargetServers"
        $DatabaseObjectList = "Assemblies","AsymmetricKeys","Certificates","Defaults","ExtendedProperties","FullTextCatalogs","PartitionFunctions","PartitionSchemes","Roles","Rules","Schemas","Synonyms","Triggers","UserDefinedAggregates","UserDefinedDataTypes","UserDefinedTypes","Users","XmlSchemaCollections"
    }

    if ($InstanceObject.Information.VersionString.substring(0,1) -eq 8)
    {
        $InstanceObjectList = "BackupDevices","Databases","LinkedServers","Logins","UserDefinedMessages"
        $JobServerObjectList = "AlertCategories","Alerts","JobCategories","Jobs","OperatorCategories","Operators","TargetServerGroups","TargetServers"
        $DatabaseObjectList = "Defaults","ExtendedProperties","FullTextCatalogs","Roles","Rules","UserDefinedDataTypes","Users"
    }

    # ScriptInstance

    Write-Host Instance
    foreach($Object in $InstanceObjectList)
    {
        Write-Host "    " $Object


        $ScripterOptions.FileName=$SchemaPath + "\Instance_" + $Object + ".sql"


        if ((Test-Path $ScripterOptions.FileName) -eq $True)
        {
            del $ScripterOptions.FileName
        }


        $InstanceObjectCombined = $InstanceObject.$Object
        $MyScripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$InstanceObjectCombined)


        if ($? -eq $False)
        {
            Write-Host "FAILED - SCRIPT ABORTED"
        }
    }

        # ScriptJobServer

    if ($InstanceObject.Information.Edition -ne 'Express Edition')
    {
        Write-Host JobServer
        foreach($Object in $JobServerObjectList)
        {
            Write-Host "    " $Object
            $ScripterOptions.FileName=$SchemaPath + "\JobServer_" + $Object + ".sql"
            if ((Test-Path $ScripterOptions.FileName) -eq $True)
            {
                del $ScripterOptions.FileName
            }
            $InstanceObjectJobServer = $InstanceObject.JobServer.$Object
            $MyScripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$InstanceObjectJobServer)
            if ($? -eq $False)
            {
                Write-Host "FAILED - SCRIPT ABORTED"
#               exit 1
            }
        }
    }


        # ScriptDatabases

    $DatabaseFilter = $InstanceObject.Databases | where {$_.Name -ne "distribution"} | where {$_.Name -ne "master"} | where {$_.Name -ne "msdb"} | where {$_.Name -ne "tempdb"}
    foreach($Database in $DatabaseFilter)
    {
        Write-Host $Database
        foreach($Object in $DatabaseObjectList)
        {
            Write-Host "    " $Object
            $ScripterOptions.FileName=$SchemaPath + "\Database_" + $Database.name + "_" + $Object + ".sql"
            if ((Test-Path $ScripterOptions.FileName) -eq $True)
            {
                del $ScripterOptions.FileName
            }
            $ScripterOptions.Permissions = $True
            $InstanceObjectDatabases = $InstanceObject.Databases[$Database.name].$Object
            $MyScripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$InstanceObjectDatabases)
            if ($? -eq $False)
            {
                Write-Host "FAILED - SCRIPT ABORTED"
#               exit 1
            }
        }

        foreach($Object in "ExtendedStoredProcedures","StoredProcedures","Tables","Views","UserDefinedFunctions")
        {
            Write-Host "    " $Object
            $ScripterOptions.FileName=$SchemaPath + "\Database_" + $Database.name + "_" + $Object + ".sql"
            if ((Test-Path $ScripterOptions.FileName) -eq $True)
            {
                del $ScripterOptions.FileName
            }
            $ObjectFilter = $InstanceObject.Databases[$Database.name].$Object | where {$_.IsSystemObject -eq $False}
            if ($ObjectFilter)
            {
                $ScripterOptions.Permissions = $True
                $MyScripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$ObjectFilter)
                if ($? -eq $False)
                {
                    Write-Host "FAILED - SCRIPT ABORTED"
#                   exit 1
                }
            }
        }
    }

    # ScriptReplication - Added after all SMO scripting to avoid bug detected in losing the database context

    $RepScriptOptions = [Microsoft.SqlServer.Replication.scriptoptions]::Creation `
    -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles `
    -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions `
    -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions

    Write-Host Replication
    foreach($ReplicatedDatabase in $RepInstanceObject.ReplicationDatabases) 
    {
        $ScriptFileName=$SchemaPath + "\Replication_" + $ReplicatedDatabase.Name + ".sql"
        if ((Test-Path $ScriptFileName) -eq $True)
        {
            del $ScriptFileName
        }
            foreach($Publication in $ReplicatedDatabase.TransPublications) 
            {
            Write-Host "    " $Publication.Name
                $Publication.Script($RepScriptOptions) >> $ScriptFileName
            if ($? -eq $False)
            {
                Write-Host "FAILED - SCRIPT ABORTED"
#               exit 1
            }
            }
            foreach($Publication in $ReplicatedDatabase.MergePublications) 
            {
            Write-Host "    " $Publication.Name
                $Publication.Script($RepScriptOptions) >> $ScriptFileName
            if ($? -eq $False)
            {
                Write-Host "FAILED - SCRIPT ABORTED"
#               exit 1
            }
            }
    }
}