Click here to Skip to main content
Click here to Skip to main content
Go to top

SMO Tutorial 3 of n - Scripting

, 27 Feb 2011
Rate this:
Please Sign up or sign in to vote.
In this article, I will show you how to use the scripting functions of SMOs.

SMOScripting.jpg

Introduction

This article is part 3 of a series of articles about programming Server Management Objects. In the first article, I have described what Server Management Objects are. We also saw how to work with database storage objects. In the second article, I have described how to use classes not related to database storage objects. In this article, I will show how to use the scripting abilities of SMOs.

Background

SQL Server client tools have very good functionality for creating database object scripts, but they don't always provide enough functionality to generate scripts the way programmers would like. These customized scripts could be used for database documentation. For that reason, database administrators and developers must code custom script generators. These generators usually generate scripts into text files that could be stored and versioned in a source control system. The custom generators could be created by using SMO objects. Scripting in SMO is controlled by the Scripter object and its child objects. A script is generated with the specified list and scripting options. The result is returned as a StringCollection system object.

To get started with SMO, first you must add a reference to Visual Studio. In the Add Reference window, select:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Mircorost.SqlServer.ConnectionInfo

When these references are added, you must add two using statements for these two namespaces:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

SMO classes for scripting operations

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.

Using the code

This simple project consists of a form called Form1. For connection to SQL Server 2005 (or later versions), it is important to use the Server class. One of the overloaded constructors of this class accepts a ServerConnection object. This object represents a connection to a server. ServerConnection has a constructor with three parameters (serverInstance, userName, and password). All parameters are loaded from the app.config file.

string serverstr = ConfigurationManager.AppSettings["Server"];
string user = ConfigurationManager.AppSettings["User"];
string password = ConfigurationManager.AppSettings["Password"];
ServerConnection conn = new ServerConnection(serverstr, user, password);
try
{
    Server server = new Server(conn);
    foreach (Database database in server.Databases)
    {
        cboDatabase.Items.Add(database.Name);
    }
    cboDatabase.SelectedIndex = 0;
}
catch (Exception err)
{
    MessageBox.Show(err.Message, "Error", 
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Using this code, you can connect to an instance of a SQL Server using credentials specified in the mentioned app.config file. When the connection is successful, dboDatabase is populated by a list of databases.

Before script generation, it is important to choose which types of objects are going to be scripted. This selection is provided in the Objects panel. You can choose from four object types (Stored Procedures, User Defined Functions, Views, and Tables). The scripting options can by chosen in the Scripting Option panel.

Script Headers Gets or sets a Boolean property value that specifies whether the generated script is prefixed with a header that contains information which includes the date and time of generation. If True, header information is included. Otherwise, False (default).
Script Permissions Gets or sets the Boolean property value that specifies whether to include all permissions in the generated script. If True, all permissions are included in the script. Otherwise, False (default).
Script Extended Properties Gets or sets the Boolean property value that specifies whether extended object properties are included in the generated script. If True, extended object properties are included in the generated script. Otherwise, False (default).
Script IF NOT EXISTS Gets or sets a Boolean property value that specifies whether to check the existence of an object before including it in the script. If True, the existence of an object is verified before including it in the script. Otherwise, False (default).
Script DROP Gets or sets the Boolean property value that specifies whether the script operation generates a Transact-SQL script to remove the referenced component. If True, the script operation generates a Transact-SQL script to remove the referenced component. If False (default), the script operation generates a Transact-SQL script to create the referenced component.
Script DB Context Gets or sets the Boolean property value that specifies whether database context is included in the generated script. If True, database context is included in the generated script. Otherwise, False (default).
Script Database If this option is selected, a Create statement for a database is created.
Script No Collation Gets or sets the Boolean property value that specifies whether to include the collation clause in the generated script. If True, the collation clause is not included. Otherwise, False (default).
Script No FileGroup Gets or sets the Boolean property value that specifies whether to include the 'ON <filegroup>' clause in the generated script. If True, the file group clause is not included in the script. Otherwise, False (default).
Script No Identities Gets or sets the Boolean property value that specifies whether definitions of identity property seed and increment are included in the generated script. If True, definitions of identity property seed and increment are not included in the generated script. Otherwise, False (default).

All properties of ScriptingOprions can be found here.

Script generation starts after clicking on the Script button. The BackgroundWorker object is initialized and the RunWorkerAsync() method is called. The BackgroundWorker class executes operations on a separate thread. I decided to use this approach because the scripting of multiple objects could cause the user interface to stop responding while the operation is running. I have added the scripting code into the DoWork event hander.

private void btnScript_Click(object sender, EventArgs e)
{
    if (backgroundWorker1.IsBusy != true)
    {
        // Start the asynchronous operation.
        backgroundWorker1.RunWorkerAsync();
    }
}

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
    BackgroundWorker worker = sender as BackgroundWorker;

    this.Invoke(new MethodInvoker(delegate
    {
        richTextScript.Text = "";
        txtProgress.Text = "";
    }));

    string serverstr = ConfigurationManager.AppSettings["Server"];
    string user = ConfigurationManager.AppSettings["User"];
    string password = ConfigurationManager.AppSettings["Password"];
    ServerConnection conn = new ServerConnection(serverstr, user, password);
    try
    {
        Server server = new Server(conn);

        string dbname = "";

        this.Invoke(new MethodInvoker(delegate
        {
            dbname = cboDatabase.SelectedItem.ToString();
        }));

        Database db = server.Databases[dbname];
        Scripter scripter = new Scripter(server);
        scripter.ScriptingProgress += 
          new ProgressReportEventHandler(ScriptingProgressEventHandler);

        ScriptingOptions so = new ScriptingOptions();
        so.IncludeIfNotExists = chkScriptIfNotExists.Checked;
        so.IncludeHeaders = chkScriptHeaders.Checked;
        so.Permissions = chkScriptPermissions.Checked;
        so.ExtendedProperties = chkScriptExtendedProperties.Checked;
        so.ScriptDrops = chkScriptDrop.Checked;
        so.IncludeDatabaseContext = chkDBContext.Checked;
        so.NoCollation = chkNoCollation.Checked;
        so.NoFileGroup = chkNoFileGroups.Checked;
        so.NoIdentities = chkNoIdentities.Checked;

        StringBuilder sbScript = new StringBuilder();

        int version = 0;

        this.Invoke(new MethodInvoker(delegate
        {
            version = cboServerVersion.SelectedIndex;
        }));

        switch (version)
        {
            case 0:
                so.TargetServerVersion = SqlServerVersion.Version80;
                break;

            case 1:
                so.TargetServerVersion = SqlServerVersion.Version90;
                break;
            case 2:
                so.TargetServerVersion = SqlServerVersion.Version100;
                break;
        }

        scripter.Options = so;

        if (chkScriptDatabase.Checked)
        {
            sbScript.Append(ScriptObject(new Urn[] { db.Urn }, scripter));
        }

        if (chkTables.Checked)
        {
            server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
            foreach (Table tb in db.Tables)
            {
                if (!tb.IsSystemObject)
                {
                    sbScript.Append(ScriptObject(new Urn[] { tb.Urn }, scripter));
                }
            }
        }

        if (chkViews.Checked)
        {
            server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), 
                                        "IsSystemObject");
            foreach (Microsoft.SqlServer.Management.Smo.View v in db.Views)
            {
                if (!v.IsSystemObject)
                {
                    sbScript.Append(ScriptObject(new Urn[] { v.Urn }, scripter));
                }
            }
        }

        if (chkUserDefinedFunctions.Checked)
        {
            server.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject");
            foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
            {
                if (!udf.IsSystemObject)
                {
                    sbScript.Append(ScriptObject(new Urn[] { udf.Urn }, scripter));
                }
            }
        }

        if (chkStoredProcedures.Checked)
        {
            server.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");

            foreach (StoredProcedure sp in db.StoredProcedures)
            {
                if (!sp.IsSystemObject)
                {
                    sbScript.Append(ScriptObject(new Urn[] { sp.Urn }, scripter));
                }
            }
        }

        this.Invoke(new MethodInvoker(delegate
        {
            richTextScript.Text = sbScript.ToString();
        }));

        Parse();
        conn.Disconnect();
    }
    catch (Exception err)
    {
        MessageBox.Show(err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

The above code demonstrated script generation. Before scripting, a connection on the server is made by the Server object using the credentials specified in the app.config file. Next, the Scripter and ScriptingOptions objects are created. A StringBuilder object sbScript is then created. Into this object, the scripts are stored, and when all the scripts are created, text from sbScript is displayed in the richTextScript object.

private string ScriptObject(Urn[] urns, Scripter scripter)
{
    StringCollection sc = scripter.Script(urns);
    StringBuilder sb = new StringBuilder();

    foreach (string str in sc)
    {
        sb.Append(str + Environment.NewLine + "GO" +
          Environment.NewLine + Environment.NewLine);
    }

    return sb.ToString();
}

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.

After all script are successfully generated, they are parsed and highlighted by the Parse() method. Keywords to be highlighted are stored in a SQL.txt file. For more information about syntax highlighting, go to:

History

  • 27 Feb 2011 - Article created.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Kanasz Robert
Architect The Staffing Edge & Marwin Cassovia Soft
Slovakia Slovakia
My name is Robert Kanasz and I have been working with ASP.NET, WinForms and C# for several years.
MCSD - Web Applications
MCSE - Data Platform
MCPD - ASP.NET Developer 3.5
- Web Developer 4
MCITP - Database Administrator 2008
- Database Developer 2008
MCSA - SQL Server 2012
MCTS - .NET Framework 3.5, ASP.NET Applications
- SQL Server 2008, Database Development
- SQL Server 2008, Implementation and Maintenance
- .NET Framework 4, Data Access
- .NET Framework 4, Service Communication Applications
- .NET Framework 4, Web Applications
MS - Programming in HTML5 with JavaScript and CSS3 Specialist
 
Open source projects: DBScripter - Library for scripting SQL Server database objects
 

Please, do not forget vote

Comments and Discussions

 
QuestionNice Pinmemberstrucker_luc18-Nov-12 3:14 
AnswerRe: Nice PinmvpKanasz Robert18-Nov-12 3:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 27 Feb 2011
Article Copyright 2011 by Kanasz Robert
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid