The basics of SMO scripting for database objects

The use of SQL Server Management Objects streamlines the process of scripting database objects, allowing DBAs and developers to customize code using basic SMO scripting know-how.

While SQL Server client tools have proven functionality for creating database object scripts, they don't always

provide enough flexibility to generate them the way you would like. For that reason, database developers and administrators have been coding custom script generation utilities and implementing automated scripting capabilities.

For example, many companies have automated the process of scripting database objects such as stored procedures and views into text files, and then checking them into a source control system like Microsoft Visual SourceSafe. Starting with SQL Server 2005, however, SQL Server Management Objects (SMO) provide the necessary API for generating database object scripts, so let’s take a look at the basics of SMO scripting and some tips to help you get started.

If you are new to SMO programming, it’s important to understand how to set up references in Visual Studio to include the SMO libraries, since much of the code in this article comes from the sample application I created to provide examples of what you can do with SMO.

Figure 1: SMO Script Generator
SMO Script Generator

The fundamentals of SMO scripting are very simple. Once you are connected to a server, select the object you want to use and invoke the Script() method. Any database object that can be created with a SQL script will have the Script() method implemented in SMO. This method also accepts an optional parameter called ScriptingOptions, which is a collection of possible scripting options with mainly true/false values that define what scripts the output should include. Learning what they are and how to use them is the most important part of programming SMO scripting.

The following code shows the scripting options included in the above sample application:

ScriptingOptions scriptingOptions = new ScriptingOptions();

scriptingOptions.ExtendedProperties = chkScriptExtendedProperties.
  Checked;
scriptingOptions.IncludeHeaders = chkScriptHeaders.Checked;
scriptingOptions.IncludeIfNotExists = chkScriptNotExists.Checked;
scriptingOptions.Permissions = chkScriptPermissions.Checked;
scriptingOptions.ScriptDrops = chkScriptDrop.Checked;
scriptingOptions.IncludeDatabaseContext = chkScriptDBContext.
  Checked;

if(cmbVersion.SelectedItem.ToString() == "SQL Server 2000")
  scriptingOptions.TargetServerVersion = SqlServerVersion.Version80;
if (cmbVersion.SelectedItem.ToString() == "SQL Server 2005")
  scriptingOptions.TargetServerVersion = SqlServerVersion.Version90;
if (cmbVersion.SelectedItem.ToString() == "SQL Server 2008")
  scriptingOptions.TargetServerVersion =
SqlServerVersion.Version100;

The first six options accept true or false boolean values, while the TargetServerVersion property determines what version of SQL Server should be targeted. The property type is the SqlServerVersion object and it supports three versions – 2000, 2005 and 2008.

In your code you can script individual objects or script all objects of a certain type. The following code shows how you can loop through all stored procedures in the database and script them into individual files:

foreach (StoredProcedure sp in db.StoredProcedures)
{
  if (sp.IsSystemObject)
    continue;

    script = ScriptObject(sp as IScriptable, scriptingOptions,
      chkScriptDrop.Checked);

      
}

Similarly, you can loop through other object types such as Views or UserDefinedFunctions. Note, however, that the code skips system objects since it’s highly unlikely that you are interested in scripting a system of stored procedures. In fact, you will get an error if you try to script system objects since the code is not scriptable.

The code calls my generic function -- ScriptObject() -- and the first parameter is an SMO interface called IScriptable. This interface allows you to cast any scriptable object into IScriptable, allowing you to invoke the Script() method on IScriptable rather than on the object type. This way I can have one generic function to handle multiple object types. For example, I can then script a view with the same function – I just have to cast the view into IScriptable the same way I did with stored procedures in the above code:

script = ScriptObject(view as IScriptable, scriptingOptions,
  chkScriptDrop.Checked);

The code in the ScriptObject() method looks like this:

private stringScriptObject(IScriptable so, ScriptingOptions
  options, bool includeDrop)
{
    StringCollection strings;
    StringBuilder sb = new StringBuilder();
    // save these values to preserve them
    bool scriptDrops = options.ScriptDrops;
    bool includeIfNotExists = options.IncludeIfNotExists;

    if(includeDrop)
  { // script DROP separately
      strings = so.Script(options);
      foreach (string str in strings)
      {
        sb.Append(str + Environment.NewLine + "GO" +
          Environment.NewLine + Environment.NewLine);
      }

      // reset DROP back to false
      options.ScriptDrops = false;
      options.IncludeIfNotExists = false;
    }

    // now script CREATE
    strings = so.Script(options);
    foreach (string str in strings)
    {
      sb.Append(str + Environment.NewLine + "GO" +
        Environment.NewLine + Environment.NewLine);
    }

    // restore the original values
    options.ScriptDrops = scriptDrops;
    options.IncludeIfNotExists = includeIfNotExists;

       return sb.ToString();
  }

The code does some special handling for a couple of scripting options. For instance, if you set the options to generate a DROP statement, it will not generate the CREATE statement. Therefore, my code calls the script method twice – once to generate the DROP statement and once to generate the CREATE statement. After DROP is generated, I can set the ScriptDrops option to false and allow CREATE to be generated.

The IncludeIfNotExists option is even trickier. If it is true together with ScriptDrops it will generate a conditional DROP looking something like this:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
           OBJECT_ID(N'[dbo].[ufnGetAccountingEndDate]') AND type in
(N'FN',    N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ufnGetAccountingEndDate]

But if the IncludeIfNotExists option is true when generating CREATE it will script the CREATE code as dynamic SQL, like this:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
              OBJECT_ID(N'[dbo].[ufnGetAccountingEndDate]') AND
type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[ufnGetAccountingEndDate]()
RETURNS [datetime]
AS
BEGIN
    RETURN DATEADD(millisecond, -2, CONVERT(datetime,
    ''2004-07-01'', 101));
END;
'

The code is generated in this format because CREATE has to be the first command in a batch for many objects. Therefore, you cannot run IF EXISTS in front of it. Since I don’t really like this format -- and I know many other DBAs feel the same -- I set this option to false after the DROP statement has been scripted. As a result, I end up dropping the object if it exists and then creating it, as opposed to creating it only if it doesn’t exist.

Also, you may have noticed that I preserved two settings in the code (ScriptDrops and IncludeIfNotExists) and at the end of the function I restore the original values. The reason for this is because the function gets called repeatedly for different objects and since we need to modify the settings inside the function for generating the CREATE script, we also need to set them back so the next object gets scripted the same way.

Here is an example of a script generated by my application after specifying to generate a conditional DROP, descriptive headers, database context and extended properties:

Figure 2: Conditional DROP script (click to enlarge)
Conditional DROP script

So those are the basics of SMO scripting, but as always there is no substitution for playing with the code, setting different scripting options and examining it. Doing so should help you generate SQL code exactly how you like it.

ABOUT THE AUTHOR
Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.

This was first published in August 2010

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close