SQL Server Magazine

The program code first reads the name of the SQL Server from the command line, and attempts to establish a connection to that SQL Server instance. We attempt to create an instance of the SMO Server class by using the following logic:

// Create an Instance of the 
// Server class.
Server theServer = new Server(str 
  DBMSInstanceName);

If the Server instantiation can't be established, the code takes an exception path and writes an error message to the screen. If the Server entity is successfully created (and the connection is successful), we attempt to retrieve the version of SQL Server. (This isn't absolutely necessary, but we do it to ensure that we really can communicate with the instance through SMO.)

Once we have a Server instance, we can begin to explore the Databases collection. The most important line in the code in this section is:

foreach (Database db in
  theServer.Databases)
{
}

This statement lets us iterate through all databases in the SMO Databases collection to find the SQL Server instance we want to document. When you look at the source code, you'll see that it deliberately avoids scripting information for the SQL Server 2000 pubs and Northwind sample databases because we don't need them for this example and will waste disk space. You might also want to exclude the SQL Server 2005 AdventureWorks sample database and some of the system databases on production systems. However, for development systems, you'll probably want to leave pubs, Northwind, and AdventureWorks in your schema output.

For each database it encounters, the code scripts out the tables, functions, views, stored procedures, and roles in that database. The logic to script individual entities is similar in each case. The code first creates a scripter object:

// Define a Scripter object and
// set the required scripting
// options.
Scripter scrp = new 
  Scripter(theServer);

Then, the code sets the appropriate options for that scripter object. For example, when scripting tables, we can include indexes and triggers in the output by setting the properties of the scripting options like this:

// Set Scripting Options.
scrp.Options.Indexes = true;
scrp.Options.Triggers = true;

Once the options are set, the code uses a foreach statement to navigate through the respective SMO entity collection for the database and scripts out the schema entities as text. The style of programming is similar regardless of whether we're scripting tables, views, or stored procedures. For example, to move through the collection of tables in a particular database, you'd use the following code:

// Iterate through Tables 
// Collection. 
foreach (Table t in db.Tables)
{ 
}

For iterating through the views collection, the code looks like this:

// Iterate through Views 
//  Collection.  
foreach (View v in db.Views)
{ 
}

And for stored procedures, the code looks like this:

// Iterate through Stored 
// Procedures Collection.
foreach (StoredProcedure sp in
   db.StoredProcedures) 
{ 
}

Note that when you're documenting database schema, it's important to include constraints. In the SchemaCollector program the line

scrp.Options.DriAll = true;

puts in all the DRI constraints. SMO is quite flexible, so you have lots of options for including and excluding various types of constraints. For more information about how to include constraints in your schema collection script, see the topic "ScriptingOptions Members" in SQL Server 2005 Books Online (BOL). You'll need to experiment within your own environment to come up with the combination of scripting attributes that you want to include in your version of the script. In Web Figure 1, you can see an example of the output I got with the table scripting options I've specified in the SchemaCollector code for this article. Also note that some filegroup information is included by default when the SchemaCollector program is run as written. (You'll notice the ON [PRIMARY] attributes that are included in the output that Web Figure 1 shows.) But full database filegroup information isn't exposed at this level. However, the "Microsoft.SqlServer. Management.Smo Namespace" topic in SQL Server 2005 BOL explains that the FileGroup and FileGroupCollection classes are exposed within that namespace. By using the Microsoft.SqlServer.Management.Smo namespace, you could, for example, query the Files property of the FileGroup member to get a list of all the data files belonging to a file group.

Prev. page     1 [2] 3     next page