Mark Gilbert's Blog

Science and technology, served light and fluffy.

SSMS Scripter Internals Part 1 of 3: “A-scripting we will go”

The first part of the SSMS Scripter that I’d like to explore is the use of SQL Management Objects (SMO).  SMO is the successor to SQL Data Management Objects (SQL-DMO), and is new to SQL Server 2005.  This collection of .NET assemblies allows you to programmatically control SQL Server.

To use these objects, I needed to add the following references to my project:

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoEnum
  • Microsoft.SqlServer.SqlEnum

Finding these was very straightforward: the respective assemblies were all named as above (for example “Microsoft.SqlServer.Smo.dll”), and were found in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\ folder on my machine.To generate the DROP and CREATE statements, we first need to create a Microsoft.SqlServer.Management.SMO.Scripter object.  This object will be configured to generate a particular kind of script (in our case, either the “drop object” script, or the “create object” script), and then used for the objects that the user selects to script out.The Scripter object is used in the SSMSScriptor.btnGenerate_Click event procedure:


‘ First, collect the DROP statements
Me.InitializeProgressBar(“Generating DROP Statements (Step 1 of 2):”)
Me
.m_scrCurrent = New Scripter(Me.CurrentServer)
Me
.m_scrCurrent.Options.ScriptDrops = True
Me.m_scrCurrent.Options.IncludeIfNotExists = True
strScripts &= Me.GenerateScripts(Me.tvwDBObjects.Nodes)
Me.m_scrCurrent = Nothing

‘ Now, collect the CREATE statements
Me
.InitializeProgressBar(“Generating CREATE Statements (Step 2 of 2):”)
Me
.m_scrCurrent = New Scripter(Me.CurrentServer)
Me
.m_scrCurrent.Options.PrimaryObject = True
strScripts &= Me.GenerateScripts(Me.tvwDBObjects.Nodes)
Me
.m_scrCurrent = Nothing

The Scripter class constructor takes a single parameter for a Microsoft.SqlServer.Management.Smo.Server object.  This object has been initialized in the SSMSScriptor class constructor:


Me.m_svrCurrent = New Server(Me.ServerName)
Me
.m_svrCurrent.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql
If
(IsNothing(Me.m_svrCurrent)) Then Throw New Exception(“An error occurred trying to establish a connection to database ‘” & Me.DatabaseName & “‘.  Please check the database name and make sure you have the appropriate permissiont to connect to it.”)

This code assumes that the current user has permissions to the named server via Windows authentication.  Pay particular attention to the SqlExecutionModes assignment as well.To generate the “DROP” statements, we need to set two properties – ScriptDrops and IncludeIfNotExists.  The former generates the “DROP …” statement, while the latter (somewhat paradoxically) wraps that in an “IF EXISTS” conditional.

Once all of the DROP statements have been collected, it’s time to generate the CREATE statements.  The Scripter object is re-instantiated, and this time the PrimaryObject property is used. All of the above is merely setting up for the actual work of scripting out the objects.  That task is done using the ToScript method of the ScripterTreeNode class.  This class inherits from the built in TreeNode class, and adds a few properties specific to the Scripter.  In particular, each object added to the tree view has an “URN” property stored.  “URN” is of type Microsoft.SqlServer.Management.Smo.Urn, and stands for “Uniform Resource Name”.  This property is used by the ToScript method to generate the requested script for the current object:

 

Public Function ToScript(ByVal scrCurrent As Scripter) As String
    Dim smoObjects(1) As Urn

    Dim scScripts As StringCollection

    Dim strCurrentScript As String

    Dim sbScripts As StringBuilder
    ‘ If this isn’t an object that should be scripted, then just exit
    If (Me.ObjectType = Common.ObjectTypeEnum.Other) Then Return “”
    smoObjects = New Urn(0) {}
    smoObjects(0) = Me.Urn

    scScripts = scrCurrent.Script(smoObjects)
    sbScripts = New StringBuilder

    For Each strCurrentScript In scScripts

        sbScripts.Append(strCurrentScript)

        sbScripts.Append(vbCrLf & “GO” & vbCrLf & vbCrLf)

    Next

    Return sbScripts.ToString
End Function

It uses a StringBuilder object to collect the scripts (there may be more than one) for the current object, and inserts a “GO” command in between each.

Finally, in the btnGenerate_Click handler, the scripts for all selected objects are collected together, and sent to the destination (file, clipboard, etc.).

For more information about SMO, please see the following links: 

In Part 2 of “SSMS Scripter Internals”, we’ll look at the pieces of the Scripter that define it as an SSMS Plug-in.

Advertisements

December 15, 2006 - Posted by | SQL Server, Tools and Toys

Sorry, the comment form is closed at this time.

%d bloggers like this: