Section 12.1. Scripting

12.1. Scripting

You can document a database schema by generating T-SQL scripts for the different objects. Possible uses for these scripts include the following:

• As a backup, allowing objects to be recreated if necessary

• To create development, testing, staging, and production environments

SQL Server Management Studio lets you script selected database objects. You can choose to use either a manual process or the Generate SQL Server Scripts Wizard.

The SMO classes used for scripting operations are described in Table 12-1. These classes are located in the Microsoft.SqlServer.Management.Smo namespace.

Table 12-1. SMO classes for scripting operations

Class	Description
Scripter	Provides programmatic access to scripting settings and functionality, including finding dependencies, outputting scripts, and managing the context of a scripting operation.
ScriptingErrorEventArgs	Represents the arguments used to report an error during a scripting operation. ScriptingErrorEventArgs is derived from EventArgs.
ScriptingOptions	Represents options for scripting operations. These options identify the SQL Server items to script and control the scripting operation.
ScriptOption	Represents a SQL Server scripting option. The ScriptOption class contains a property for each type of SQL Server item that can be scripted.

This section shows how to programmatically use SMO scripting classes. You need to add a reference to the Microsoft.SqlServer.SmoEnum assembly to compile and execute the examples in this section.

A Uniform Resource Name (URN) address uniquely identifies each SQL Server object. The first example and the next one use URNs to specify which objects to script.

The first example shows how to use the Scripter class to script the objects in the AdventureWorks database:

     using System;     using System.Data;     using System.Collections.Specialized;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             Scripter scripter = new Scripter(server);             StringCollection sc = scripter.Script(new Urn[] { db.Urn });             foreach (string s in sc)                 Console.WriteLine(s);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 12-1.

Figure 12-1. Results for scripting AdventureWorks database objects

The Script( ) method of the Scripter class generates T-SQL that can be used to create SQL Server objects identified by either a SqlSmoObject array, Urn array, or UrnCollection object passed as an argument to the constructor. The Script( ) method returns the T-SQL as a StringCollection object. The Options property exposes a ScriptingOptions object that lets you control scripting operations.

The following example scripts all tables in AdventureWorks:

     using System;     using System.Data;     using System.Collections.Specialized;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             UrnCollection urnc = new UrnCollection(  );             foreach (Table t in db.Tables)                 urnc.Add(t.Urn);             Scripter scripter = new Scripter(server);             StringCollection sc = scripter.Script(urnc);             foreach (string s in sc)                 Console.WriteLine(s);             Console.WriteLine(Environment.NewLine + "Press any key to continue." );             Console.ReadKey(  );         }     } 

Partial results are shown in Figure 12-2.

Figure 12-2. Results for scripting AdventureWorks tables using UrnCollection class example

The Script( ) method of the Scripter class is overloaded. In addition to accepting a UrnCollection object as shown in the preceding example, it can also take a list of objects as a Urn array or a SqlSmoObject array.

The SMO classes used to manage URN addresses are described in Table 12-2.

Table 12-2. SMO classes for managing URN addresses

Class	Description
Urn	Represents a URN address
UrnCollection	Represents a collection of Urn objects

The SqlSmoObject class is a generic class that represents all SQL Server objects. The following example uses the SqlSmoObject class to produce the same result as the preceding example:

     using System;     using System.Data;     using System.Collections.Specialized;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             Table[] ta = new Table[db.Tables.Count];             db.Tables.CopyTo(ta, 0);             SqlSmoObject[] ssoa = new SqlSmoObject[db.Tables.Count];             Array.Copy(ta, ssoa, ta.Length);             Scripter scripter = new Scripter(server);             StringCollection sc = scripter.Script(ssoa);             foreach (string s in sc)                 Console.WriteLine(s);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Partial results are shown in Figure 12-3.

The static EnumDependencies( ) method of the Scripter class returns a UrnCollection object that contains the specified object and either its parents or children, depending on the value of the second argument from the DependencyType enumeration.

The following example uses the EnumDependencies( ) method to script the first table in AdventureWorks and its child objects:

     using System;     using System.Data;     using System.Collections.Specialized;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             Scripter scripter = new Scripter(server);             UrnCollection urnc = Scripter.EnumDependencies(                 db.Tables[0], DependencyType.Children);             StringCollection sc = scripter.Script(urnc);             foreach (string s in sc)                 Console.WriteLine(s);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Figure 12-3. Results for scripting AdventureWorks tables using SqlSmoObject class example

Results are shown in Figure 12-4.

The output contains the T-SQL script to generate both the table named AWBuildVersion and its childrena single update trigger on the table.

The Scripter object raises three types of events, described in Table 12-3.

Figure 12-4. Results for scripting single AdventureWorks table and its children example

Table 12-3. Scripter events

Event	Description
DiscoveryProgress	Reports progress at intervals during the DiscoverDependencies( ) method
ScriptingError	Reports an error during a scripting operation
ScriptingProgress	Reports progress at intervals during the Script( ) method

This example receives and handles the ScriptingProgress event:

     using System;     using System.Data;     using System.Collections.Specialized;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             UrnCollection urnc = new UrnCollection(  );             foreach (Table t in db.Tables)                 urnc.Add(t.Urn);             Scripter scripter = new Scripter(server);             scripter.ScriptingProgress +=                 new ProgressReportEventHandler(ScriptingProgressEventHandler);             StringCollection sc = scripter.Script(urnc);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }         protected static void ScriptingProgressEventHandler(             object sender, ProgressReportEventArgs e)         {             Console.WriteLine("(" + e.TotalCount + "/" + e.Total + ") " + e.Current);         }     } 

Partial results are shown in Figure 12-5.

Figure 12-5. Results for monitoring scripting progress example