How to automate script generation using SMO in SQL Server?

Question

I would like to automate script generation (in SSMS --> Tasks --> Generate Scripts) in SSMS 2008. I have read that SQL Server 2008 does not support Database Publishing Wizard (including SQLPUBWIZ SCRIPT) but this automation can be done using SMO in SQL Server 2008. I don't have a clue about SMO and how to do this using SMO, so could you give me some advice (resources etc.) how to begin?

Dispensable

possible duplicate of Automate Generate Scripts Wizard in SQL2008 Or this too stackoverflow.com/questions/3384649 – Adallard

Answer 1

The key to SMO scripting is the Scripter class. All other tools (like SSMS) use this class to generated object creation scripts. There is an example usage on MSDN:

{ 
   //Connect to the local, default instance of SQL Server. 
  Server srv = new Server(); 

   //Reference the AdventureWorks2008R2 database.  
  Database db = srv.Databases["AdventureWorks2008R2"]; 

   //Define a Scripter object and set the required scripting options. 
  Scripter scrp = new Scripter(srv); 
   scrp.Options.ScriptDrops = false; 
   scrp.Options.WithDependencies = true; 

   //Iterate through the tables in database and script each one. Display the script. 
   //Note that the StringCollection type needs the System.Collections.Specialized namespace to be included. 
   Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] smoObjects = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[1] ;
   foreach (Table tb in db.Tables) {   
      smoObjects[0] = tb.Urn; 
      if (tb.IsSystemObject == false) { 
         System.Collections.Specialized.StringCollection sc;
         sc = scrp.Script(smoObjects); 
         foreach ( string st in sc) { 
            Console.WriteLine(st); 
         } 
      } 
   } 
}

Reminiscence

Answer 2

Although the question has been accurately answered, I struggled for a few days to put together a script that scripted out all the objects I cared about on a database server. Here's my code just in case it is useful to someone else at some point.

# Load needed assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") | Out-Null;

#Specify target server and databases.
$sql_server = "SomeServerName"
$SMOserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList "$sql_server"
$databases = $SMOserver.Databases
$BaseSavePath = "T:\SomeFilePath\" + $sql_server + "\"

#Remove existing objects.
Remove-Item $BaseSavePath -Recurse

#Script server-level objects.
$ServerSavePath = $BaseSavePath
$ServerObjects = $SMOserver.BackupDevices
$ServerObjects += $SMOserver.Endpoints
$ServerObjects += $SMOserver.JobServer.Jobs
$ServerObjects += $SMOserver.LinkedServers
$ServerObjects += $SMOserver.Triggers

foreach ($ScriptThis in $ServerObjects | where { !($_.IsSystemObject) }) {
    #Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name
    $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
    $scriptr.Options.AppendToFile = $True
    $scriptr.Options.AllowSystemObjects = $False
    $scriptr.Options.ClusteredIndexes = $True
    $scriptr.Options.DriAll = $True
    $scriptr.Options.ScriptDrops = $False
    $scriptr.Options.IncludeHeaders = $False
    $scriptr.Options.ToFileOnly = $True
    $scriptr.Options.Indexes = $True
    $scriptr.Options.Permissions = $True
    $scriptr.Options.WithDependencies = $False

    <#Script the Drop too#>
    $ScriptDrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
    $ScriptDrop.Options.AppendToFile = $True
    $ScriptDrop.Options.AllowSystemObjects = $False
    $ScriptDrop.Options.ClusteredIndexes = $True
    $ScriptDrop.Options.DriAll = $True
    $ScriptDrop.Options.ScriptDrops = $True
    $ScriptDrop.Options.IncludeHeaders = $False
    $ScriptDrop.Options.ToFileOnly = $True
    $ScriptDrop.Options.Indexes = $True
    $ScriptDrop.Options.WithDependencies = $False

    <#This section builds folder structures.  Remove the date folder if you want to overwrite#>
    $TypeFolder = $ScriptThis.GetType().Name
    if ((Test-Path -Path "$ServerSavePath\$TypeFolder") -eq "true") `
    { "Scripting Out $TypeFolder $ScriptThis" } `
        else { new-item -type directory -name "$TypeFolder"-path "$ServerSavePath" }
    $ScriptFile = $ScriptThis -replace ":", "-" -replace "\\", "-"
    $ScriptDrop.Options.FileName = $ServerSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"
    $scriptr.Options.FileName = $ServerSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"

    #This is where each object actually gets scripted one at a time.
    $ScriptDrop.Script($ScriptThis)
    $scriptr.Script($ScriptThis)
} #This ends the object scripting loop at the server level.

#Script database-level objects.
foreach ($db in $databases) {
    $DatabaseObjects = $db.ApplicationRoles
    $DatabaseObjects += $db.Assemblies
    $DatabaseObjects += $db.ExtendedStoredProcedures
    $DatabaseObjects += $db.ExtendedProperties
    $DatabaseObjects += $db.PartitionFunctions
    $DatabaseObjects += $db.PartitionSchemes
    $DatabaseObjects += $db.Roles
    $DatabaseObjects += $db.Rules
    $DatabaseObjects += $db.Schemas
    $DatabaseObjects += $db.StoredProcedures
    $DatabaseObjects += $db.Synonyms
    $DatabaseObjects += $db.Tables
    $DatabaseObjects += $db.Triggers
    $DatabaseObjects += $db.UserDefinedAggregates
    $DatabaseObjects += $db.UserDefinedDataTypes
    $DatabaseObjects += $db.UserDefinedFunctions
    $DatabaseObjects += $db.UserDefinedTableTypes
    $DatabaseObjects += $db.UserDefinedTypes
    $DatabaseObjects += $db.Users
    $DatabaseObjects += $db.Views

    #Build this portion of the directory structure out here.  Remove the existing directory and its contents first.
    $DatabaseSavePath = $BaseSavePath + "Databases\" + $db.Name

    new-item -type directory -path "$DatabaseSavePath"

    foreach ($ScriptThis in $DatabaseObjects | where { !($_.IsSystemObject) }) {
        #Need to Add Some mkDirs for the different $Fldr=$ScriptThis.GetType().Name
        $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
        $scriptr.Options.AppendToFile = $True
        $scriptr.Options.AllowSystemObjects = $False
        $scriptr.Options.ClusteredIndexes = $True
        $scriptr.Options.DriAll = $True
        $scriptr.Options.ScriptDrops = $False
        $scriptr.Options.IncludeHeaders = $False
        $scriptr.Options.ToFileOnly = $True
        $scriptr.Options.Indexes = $True
        $scriptr.Options.Permissions = $True
        $scriptr.Options.WithDependencies = $False

        <#Script the Drop too#>
        $ScriptDrop = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver)
        $ScriptDrop.Options.AppendToFile = $True
        $ScriptDrop.Options.AllowSystemObjects = $False
        $ScriptDrop.Options.ClusteredIndexes = $True
        $ScriptDrop.Options.DriAll = $True
        $ScriptDrop.Options.ScriptDrops = $True
        $ScriptDrop.Options.IncludeHeaders = $False
        $ScriptDrop.Options.ToFileOnly = $True
        $ScriptDrop.Options.Indexes = $True
        $ScriptDrop.Options.WithDependencies = $False

        <#This section builds folder structures.  Remove the date folder if you want to overwrite#>
        $TypeFolder = $ScriptThis.GetType().Name
        if ((Test-Path -Path "$DatabaseSavePath\$TypeFolder") -eq "true") `
        { "Scripting Out $TypeFolder $ScriptThis" } `
            else { new-item -type directory -name "$TypeFolder"-path "$DatabaseSavePath" }
        $ScriptFile = $ScriptThis -replace ":", "-" -replace "\\", "-"
        $ScriptDrop.Options.FileName = $DatabaseSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"
        $scriptr.Options.FileName = $DatabaseSavePath + "\" + $TypeFolder + "\" + $ScriptFile.Replace("]", "").Replace("[", "") + ".sql"

        #This is where each object actually gets scripted one at a time.
        $ScriptDrop.Script($ScriptThis)
        $scriptr.Script($ScriptThis)

    } #This ends the object scripting loop.
} #This ends the database loop.

Skein

Answer 3

There a few ways of scripting all objects in database, but one of the easiest is to you the SMO tranfer class. Here's some PowerShell code to script out all objects:

add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

$sourceSrv = "$env:computername\sql2k8"
$sourceDb = "Northwind"

$server =  new-object ("Microsoft.SqlServer.Management.Smo.Server") $sourceSrv
$db = $server.Databases[$sourceDb]

$transfer = new-object ("Microsoft.SqlServer.Management.Smo.Transfer") $db
$transfer.CopyAllObjects = $true
$transfer.DropDestinationObjectsFirst = $true
$transfer.CopySchema = $true
$transfer.Options.IncludeIfNotExists = $true

$transfer.ScriptTransfer()

Pleione

this answer should be higher! – Actin