Sri says:

# Many thanks to Brian Brooks for this script that help me so much.
#This one does not generate one per table but 1 per object type.
#You should have your list of intances in instance_list.txt
#The output is generated in .\sqlschemas folder

# LoopInstances

$InstanceListFile = “.\instance_list.txt”

$InstanceList = Get-Content $InstanceListFile
foreach ($InstanceName in $InstanceList)
{
# Configuration

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | 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”

# $mySrvConn = New-Object “Microsoft.SqlServer.Management.Common.ServerConnection”
# $mySrvConn.ServerInstance=$InstanceName
# $mySrvConn.LoginSecure = $false
# $mySrvConn.Login = “sa”
# $mySrvConn.Password = “”

$InstanceObject = New-Object “Microsoft.SqlServer.Management.SMO.Server” “$InstanceName”
$RepInstanceObject = New-Object “Microsoft.SqlServer.Replication.ReplicationServer” “$InstanceName”
# $InstanceObject = New-Object “Microsoft.SqlServer.Management.SMO.Server” ($mySrvConn)
# $RepInstanceObject = New-Object “Microsoft.SqlServer.Replication.ReplicationServer” ($mySrvConn)

$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

# SetSchemaPath

$CurrentDay = Get-Date -Format yyyyMMdd
$SchemaPath = “.\SQLSchemas\” + $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
}
$MyScripter.Script($InstanceObject.$Object)
if ($? -eq $False)
{
Write-Host “FAILED – SCRIPT ABORTED”
exit 1
}
}

# 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
}
$MyScripter.Script($InstanceObject.JobServer.$Object)
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 “ReportServer”} | where {$_.Name -ne “ReportServerTempDB”} | 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
$MyScripter.Script($InstanceObject.Databases[$Database.name].$Object)
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($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
}
}
}
}

Sri says:

Amen to elegance but when u want to script all instances in your environment , including server level objects and each and every db object , I struggle to find elegant solutions. Let me know if you find one.