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?
+2
A:
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);
}
}
}
}
Remus Rusanu
2010-08-15 21:59:10
+2
A:
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()
Chad Miller
2010-08-16 00:44:23