Scripting

In many cases you need to get a DDL script to re-create an object, possibly against a different server. For this purpose you will use a feature called scripting. Any object that implements the IScriptable interface uses two overloaded Script methods. The first method takes no parameters and the second method accepts different scripting options via a special class called ScriptingOptions. Both methods return StringCollection, which contains the DDL for this particular object. Using one of these two scripting methods of the object is by far the simplest way to obtain its script. However, this is not the only way to obtain a script and does not satisfy a complex scenario of scripting several objects at once while respecting their dependencies. For example, when you have a view V1 that relies on another view V2 and that view in turn relies on table T1, you likely want to generate the script in the right order, that is, T1, V2, V1. To accomplish this, you need to use a standalone Scripter object. In a simple case, you instantiate the Scripter object, supply it with a list of objects to script and some scripting options, and get back a complete DDL for creation of all the objects you specified plus any dependent objects Scripter discovers. However, this happens only if you set the WithDependencies flag to true on the ScriptingOptions object.

Tip

Scripter also enables you to generate drop statements for the objects in the proper sequence. To accomplish this you should set the aforementioned WithDependenices option as well as the ScriptDrops flag.

A single-phase scripting is shown in Figure 11-2. Notice how the Scripter is treated as a "black box" by the user. In other words, after scripting is invoked, you expect to get the full script back in one section without interruption.

Figure 11-2. SMO scripting in a single phase.

Let's look at a code sample for this scenario.

Single Phase Scripting

Create a Scripter object by passing a reference to previously instantiated server object (see earlier examples):

Visual Basic .NET

Dim scripterInstance As Scripter scripterInstance = new Scripter(serverInstance) 

C# .NET

Scripter scripterInstance = new Scripter(serverInstance); 

Set different scripting options:

scripterInstance.Options.WithDependencies = true; scripterInstance.Options.ClusteredIndexes = true; scripterInstance.Options.NonClusteredIndexes = true; scripterInstance.Options.DriPrimaryKey = true; scripterInstance.Options.DriUniqueKeys = true; 

Generate a list of objects to script:

Visual Basic .NET

Dim db As Database Dim scriptList(2) As SqlSmoObject db = serverInstance.Databases("pubs"); scriptList(1) = db.Tables("authors") scriptList(2) = db.Tables("publishers") 

C# .NET

[View full width]
Database db = serverInstance.Databases["pubs"]; SqlSmoObject[] scriptList = new SqlSmoObject[] {db.Tables["authors"], db .Tables["publishers"]}; 

Now invoke scripting:

Visual Basic .NET

Dim scriptStrings As StringCollection scriptStrings = scripterInstance.Script(scriptList); 

C# .NET

scriptStrings = scripterInstance.Script(scriptList); 

Behind the Scenes

Let's examine what happens behind the scenes and consider some of the advanced scripting features exposed by the Scripter object. Figure 11-3 shows multiple scripting phases.

Figure 11-3. SMO scripting in multiple phases.

The first scripting phase is called Dependency Discovery or Discovery for short. This is when SMO figures out object interdependencies and builds an internal tree of objects. To accomplish this in code you write the following:

DependencyTree depTree = scripter.DiscoverDependencies (smoObjectsList, true); 

Tip

The Scripter class conveniently extends the DependencyWalker class that actually provides this functionality, so you could work with the DependencyWalker class directly if you want to only generate the script at the end.

After Discovery is finished, the user has a chance to manipulate the dependency tree and remove unnecessary branches for objects that do not need to be scripted. The user can navigate the dependency tree in a simple manner. Each node in a tree is represented by the DependencyTreeNode object, which can have any number of dependent child nodes. You can walk through each level of dependent child objects as follows:

If (depParent.HasChildNodes) { depChild = depParent.FirstChild;      while ( null != depChild ) {              // do something with the node      depChild = depChild.NextSibling;      } } 

While navigating the tree, you can remove any of its branches by calling the Remove method on any node. This causes the particular node and any of its dependent nodes to not be scripted.

The second scripting phase is called List Generation. During this phase, the scripter walks the dependency tree and comes up with a flat list of objects for scripting. This is how you can perform the navigation through the Scripter object:

DependencyCollection depCollection = scripter.WalkDependencies(dependencyTree); 

After a list of objects is generated, it may be desirable for a user to manipulate it further by adding or removing nodes.

The final phase of the scripting process is called Script Generation and actually involves scripting each object on the list individually. Here is how you accomplish it:

StringCollection scriptBatches = scr.ScriptWithList(dependencyCollection); 

Previously, you saw how scripting can be performed in a single step. Here is how you can invoke each phase individually and accomplish the same goal but with more granular control of the process, as was shown in Figure 11-3.

Advanced Scripting

Rather than call the Script method, as the previous example did, let's call each scripting phase individually:

Visual Basic .NET

Dim depTree As DependencyTree Dim depCollection As DependencyCollection Dim scriptBatches As StringCollection depTree = scripterInstance.DiscoverDependencies (scriptList, true); 'do something with a tree ' ... depCollection = scripterInstance.WalkDependencies(depTree); 'do something with a list ' ... scriptBatches = scripterInstance.ScriptWithList(depCollection); 

C# .NET

DependencyTree dependencyTree = scripterInstance.DiscoverDependencies (scriptList, true); // do something with a tree // ... DependencyCollection dependencyCollection = scripterInstance.WalkDependencies(dependencyTree); // do something with a list // ... StringCollection scriptBatches = scripterInstance.ScriptWithList(dependencyCollection); 

This discussion of scripting would not be complete without mention of the advanced scripting callback mechanism. Users can sign up to be notified via a callback function during the dependency walk before each tree node is added to the flat list of objects for scripting. Another notification point is during the scripting phase, when the script for the object is about to be generated. Here is how you can declare a filter function and sign up for the event:

Visual Basic .NET

Public Function CustomScriptingFilter(objectUrn As Urn) As Boolean 'logic to determine whether object needs to be skipped. 'If so, return True Return False End Function ' CustomScriptingFilter 

C# .NET

bool CustomScriptingFilter(Urn urn) {       // logic to determine whether object needs to       // be skipped. If so, return true.       return false; } 

Sign up for the event before the desired stage of the scripting process as follows:

Visual Basic .NET

scripter.FilterCallbackFunction = New ScriptingFilter (AddressOf CustomScriptingFilter); 

C# .NET

scripter.FilterCallbackFunction = new ScriptingFilter (CustomScriptingFilter); 

Tip

If you want to script objects for the entire database, you should consider using the TRansfer object instead of Scripter. In addition to transferring metadata between two databases via script, the TRansfer object can also move actual data by utilizing a specialized SQL Server Integration Services task. Here is an example of how you can generate a script for all tables and views inside the database for creation on a SQL Server 2005 server:

Transfer transfer = new Transfer(database); transfer.CopyAllObjects = false; transfer.CopyAllTables = true; transfer.CopyAllViews = true; transfer.Options.TargetServerVersion = SqlServerVersion.Version90; StringCollection scriptBatches = transfer.ScriptTransfer();