Export (0) Print
Expand All

Scripting

Scripting in SMO is controlled by the Scripter object and its child objects, or the Script method on individual objects. The Scripter object controls the mapping out of dependency relationships for objects on an instance of Microsoft SQL Server.

Advanced scripting by using the Scripter object and its child objects is a three phase process:

  1. Discovery

  2. List generation

  3. Script generation

The discovery phase uses the DependencyWalker object. Given an URN list of objects, the DiscoverDependencies method of the DependencyWalker object returns a DependencyTree object for the objects in the URN list. The Boolean fParents parameter is used to select whether the parents or the children of the specified object are to be discovered. The dependency tree can be modified at this stage.

In the list generation phase, the tree is passed in and the resulting list is returned. This object list is in scripting order and can be manipulated.

The list generation phases use the WalkDependencies method to return a DependencyTree. The DependencyTree can be modified at this stage.

In the third and final phase, a script is generated with the specified list and scripting options. The result is returned as a StringCollection system object. In this phase the dependent object names are then extracted from the Items collection of the DependencyTree object and properties such as NumberOfSiblings and FirstChild.

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see How to: Create a Visual Basic SMO Project in Visual Studio .NET or How to: Create a Visual C# SMO Project in Visual Studio .NET.

This code example requires an Imports statement for the System.Collections.Specialized namespace. Insert this with the other Imports statements, before any declarations in the application.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Collections.Specialized

This code example shows how to discover the dependencies and iterate through the list to display the results.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Scripter object and set the required scripting options.
Dim scrp As 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.
Dim tb As Table
Dim smoObjects(1) As Urn
For Each tb In db.Tables
    smoObjects = New Urn(0) {}
    smoObjects(0) = tb.Urn
    If tb.IsSystemObject = False Then
        Dim sc As StringCollection
        sc = scrp.Script(smoObjects)
        Dim st As String
        For Each st In sc
            Console.WriteLine(st)
        Next
    End If
Next


This code example shows how to discover the dependencies and iterate through the list to display the results.

//Connect to the local, default instance of SQL Server. 
{ 
   Server srv = default(Server); 
   srv = new Server(); 
   //Reference the AdventureWorks database. 
   Database db = default(Database); 
   db = srv.Databases("AdventureWorks"); 
   //Define a Scripter object and set the required scripting options. 
   Scripter scrp = default(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. 
   Table tb = default(Table); 
   Urn[] smoObjects = new Urn[2]; 
   foreach ( tb in db.Tables) { 
      smoObjects = new Urn[1]; 
      smoObjects(0) = tb.Urn; 
      if (tb.IsSystemObject == false) { 
         StringCollection sc = default(StringCollection); 
         sc = scrp.Script(smoObjects); 
         string st = null; 
         foreach ( st in sc) { 
            Console.WriteLine(st); 
         } 
      } 
   } 
} 
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft