Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2008

SMO Tutorial 3 of n - Scripting

Rate me:
Please Sign up or sign in to vote.
4.93/5 (50 votes)
27 Feb 2011CPOL6 min read 104.3K   3.8K   74   44
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:

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

SMO classes for scripting operations

ScripterProvides programmatic access to scripting settings and functionality, including finding dependencies, outputting scripts, and managing the context of a scripting operation.
ScriptingErrorEventArgsRepresents the arguments used to report an error during a scripting operation. ScriptingErrorEventArgs is derived from EventArgs.
ScriptingOptionsRepresents options for scripting operations. These options identify the SQL Server items to script and control the scripting operation.
ScriptOptionRepresents 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.

C#
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 HeadersGets 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 PermissionsGets 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 PropertiesGets 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 EXISTSGets 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 DROPGets 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 ContextGets 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 DatabaseIf this option is selected, a Create statement for a database is created.
Script No CollationGets 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 FileGroupGets 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 IdentitiesGets 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.

C#
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.

C#
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)


Written By
Architect 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

 
QuestionScripting User Defined Table types using SMO Pin
Member 1204099720-Oct-15 4:50
Member 1204099720-Oct-15 4:50 
Questionthousand of tables, this will be low efficient. Pin
Hua Yujun3-Apr-13 1:53
Hua Yujun3-Apr-13 1:53 
AnswerRe: thousand of tables, this will be low efficient. Pin
Kanasz Robert24-Apr-13 22:37
professionalKanasz Robert24-Apr-13 22:37 
QuestionAnother good one Pin
xmaster123_212-Mar-13 23:56
xmaster123_212-Mar-13 23:56 
AnswerRe: Another good one Pin
Kanasz Robert13-Mar-13 3:16
professionalKanasz Robert13-Mar-13 3:16 
QuestionNice Pin
strucker_luc18-Nov-12 3:14
strucker_luc18-Nov-12 3:14 
AnswerRe: Nice Pin
Kanasz Robert18-Nov-12 3:22
professionalKanasz Robert18-Nov-12 3:22 
QuestionInteresting article and very helpful Pin
kr1234564-Nov-12 3:56
kr1234564-Nov-12 3:56 
AnswerRe: Interesting article and very helpful Pin
Kanasz Robert4-Nov-12 4:04
professionalKanasz Robert4-Nov-12 4:04 
Questiongood Pin
superdevX151-Nov-12 6:49
superdevX151-Nov-12 6:49 
AnswerRe: good Pin
Kanasz Robert1-Nov-12 6:56
professionalKanasz Robert1-Nov-12 6:56 
Questionvery well written article Pin
hakon12331-Oct-12 5:28
hakon12331-Oct-12 5:28 
AnswerRe: very well written article Pin
Kanasz Robert31-Oct-12 5:38
professionalKanasz Robert31-Oct-12 5:38 
Questioninteresting Pin
memlon mulas29-Oct-12 5:13
memlon mulas29-Oct-12 5:13 
AnswerRe: interesting Pin
Kanasz Robert29-Oct-12 5:20
professionalKanasz Robert29-Oct-12 5:20 
Questiongood and well written article Pin
jackhoal27-Oct-12 3:54
jackhoal27-Oct-12 3:54 
AnswerRe: good and well written article Pin
Kanasz Robert27-Oct-12 4:01
professionalKanasz Robert27-Oct-12 4:01 
QuestionExcellent Pin
windevvv21-Oct-12 6:48
windevvv21-Oct-12 6:48 
AnswerRe: Excellent Pin
Kanasz Robert21-Oct-12 7:00
professionalKanasz Robert21-Oct-12 7:00 
Questiongood Pin
kaslaninovic2-Oct-12 22:47
kaslaninovic2-Oct-12 22:47 
AnswerRe: good Pin
Kanasz Robert3-Oct-12 6:51
professionalKanasz Robert3-Oct-12 6:51 
Question5 Pin
developer88123-Sep-12 3:00
developer88123-Sep-12 3:00 
AnswerRe: 5 Pin
Kanasz Robert23-Sep-12 23:10
professionalKanasz Robert23-Sep-12 23:10 
QuestionGreat article Pin
bikerius19-Sep-12 2:04
bikerius19-Sep-12 2:04 
AnswerRe: Great article Pin
Kanasz Robert19-Sep-12 4:25
professionalKanasz Robert19-Sep-12 4:25 

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

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