RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server Management Objects: Lessons from the Wild : Page 3

Harness the power of SQL Server Management Objects to create, document, and manage your SQL Server databases.

Performance Issues
As you will recall, one reason to use SMO instead of DMO is increased performance. In addition to delaying connections to SQL Server, SMO has increased performance by limiting the amount of information retrieved for each of the objects provided in the framework. SMO loads only a minimum set of properties when you create an object; in other words, the objects are only partially initialized. If you later attempt to read an uninitialized property, SMO automatically makes a request to the server to retrieve the property details. Used improperly, however, you can quickly lose the performance gain provided by partially initialized objects.

The sample application demonstrates an example of this kind of performance loss. The application doesn't show system objects by design; instead, it queries the IsSystemObject property of each object to determine whether to display the object. Unfortunately the application took a long time to load even a very simple schema into the tree view after this feature was added because the IsSystemObject property is not among the properties fetched by default, so querying the property on every object resulted in individual calls back to SQL Server to retrieve this property for each object.

Thoughtfully, Microsoft lets you alter the set of field values initialized by default. For example, to make the IsSystemObject one of the default initialized fields when creating objects, the Server class exposes a SetDefaultInitFields method. The following code shows how to use the SetDefaultInitFields method to fetch the IsSystemObject property for the Table, View, and StoredProcedure classes. Notice that you must request the property name (IsSystemObject, in this case), for each type of object you plan to query it from. The sample application checks the IsSystemObject property for tables, views, and stored procedures, so it requires three calls to SetDefaultInitFields:

   05 m_objServer.SetDefaultInitFields(GetType(Table), "IsSystemObject")
   10 m_objServer.SetDefaultInitFields(GetType(Smo.View), "IsSystemObject")
   15 m_objServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")
After adding that code, the sample application loaded a database schema much faster.

Default Constraints and Primary Keys
Unlike check constraints, which exist at the table level in the SMO hierarchy, default constraints are properties of the table columns. The sample application populates a list of DefaultConstraint objects as it traverses the list of columns for each table. Then, after adding the table columns to the tree view, it adds the list of DefaultConstraints:

   05 Dim aobjTableDefaultCons As New List(Of DefaultConstraint)
   . . .
   30 For Each objTableColumn As Column In objSMOTable.Columns
   . . .
   55   If Not IsNothing(objTableColumn.DefaultConstraint) Then
   60     aobjTableDefaultCons.Add(objTableColumn.DefaultConstraint)
   65   End If
   . . .
   85 Next
The sample application treats primary keys similarly, which are not a property of the SMO table class. Instead, you can discover the primary key of a table by looping through a table's indexes and querying the IndexKeyType property of each index. An index with its IndexKeyType set to DriPrimaryKey is the primary key, or part of the primary key, for that table. Here's an example:

   05 For Each objIndex As Index In objSMOTable.Indexes
   . . .
   30   If objIndex.IndexKeyType = IndexKeyType.DriPrimaryKey Then
   . . .
          ' This index is the primary key for the table
   . . .
   55   End If
   . . .
   85 Next
Scripting SQL Server Objects
Until now, this article has focused on discovering SQL objects using SMO, but SMO is also useful for generating the T-SQL scripts that define many SQL Server objects. In most enterprises, database administrators use SQL Server Management Studio to generate scripts so they can push schema changes from one database to another. To demonstrate SMO's script-generation features, the tree nodes for the tables, views, and stored procedures in the sample application's tree view have a context menu that you can use to script the object. After creating the script using SMO, the sample application places the script on the clipboard.

Using the defaults provided by SMO, you generate an object's script simply by calling the Script method on that object. The default script is generally very simple, including only the object creation code. For example, here's a table-creation script generated from the sample application using the default scripting options:

   CREATE TABLE [dbo].[PeopleIKnow](
      [FriendID] [int] IDENTITY(1,1) NOT NULL,
      [FirstName] [varchar](50) 
         COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [LastName] [varchar](50) 
         COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Nickname] [varchar](50) 
         COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Friend] [int] NULL,
      [College] [int] NULL
Author's Note: The output from the Script method does not include the GO statements shown above. Instead, the Script method returns a StringCollection where each string in the collection contains one of the statements shown above. The sample application makes the script more usable by adding GO statements after each string in the StringCollection, similar to the behavior of SQL Server Management Studio.

Such basic object-creation scripts often aren't sufficient; most scripts will also need to include script to create related objects. For example, a table-creation script will also need to include indexes, permissions, constraints, etc. To modify the default scripting behavior of SMO, you populate a ScriptingOptions object and pass it as a parameter to the object's Script method, as in the example below:

   05 Dim strc As StringCollection
   10 Dim objScriptingOptions As ScriptingOptions
   . . .
   25 objScriptingOptions = New ScriptingOptions
   30 objScriptingOptions.ClusteredIndexes = True
   35 objScriptingOptions.NonClusteredIndexes = True
   40 objScriptingOptions.DriAll = True
   45 objScriptingOptions.Indexes = True
   50 objScriptingOptions.IncludeDatabaseContext = True
   55 objScriptingOptions.Permissions = True
   . . .
   75 strc = objTable.Script(objScriptingOptions)
You can find complete documentation for the ScriptingOptions class on MSDN. Listing 1 shows a script generated for the same table shown earlier, but with the scripting behavior modified to include indexes, permissions, constraints, and so on.

Perhaps you have found that SQL Server Management Studio, while very useful, is lacking some essential tools you need to manage SQL Server in your enterprise. For example, one common complaint about SQL Server Management Studio is that its scripting capabilities don't produce both DROP and CREATE statements in the same script. Most developers who script schema changes and push them to other platforms find they need to build both DROP and CREATE scripts for most objects. As you've seen, you can use SMO to do that as well as build other custom applications tailored to the specific needs of your enterprise.

Michael S. Jones is the Director of Systems Architecture for Passport Health Communications, Inc., a national healthcare technology provider connecting hospitals, physician clinics and outpatient centers with payer and patient information to facilitate and improve their revenue cycle process. Michael, his wife, and three children live in Franklin, Tennessee where he spends his spare time reading and enjoying time with his family outdoors. .
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date