Getting Results
The output of the SchemaCollector program is shown in Figure 3. Each schema
entity (e.g., table, view, procedure) gets
scripted to its own individual file. This individual scripting makes it easy to archive all
schema entities in a source-code repository
such as Visual SourceSafe. We manufacture
the appropriate script filename based on
the entity name, appending the .sql suffix so
that we know it's a script file. For example,
for tables we use:
// Set the Script
// File Name.
scrp.Options.
FileName =
strScriptFileDir +
@"\" + strTableName
+ ".SQL";
To do the actual scripting,
we need to reference a
Uniform Resource Name
(URN). This is a special
address (or handle) that
uniquely identifies SQL
Server objects. The SMO
coding looks like this:
// Script Out This
// Table.
Urn\[] urn = new
Urn\[1];
urn\[0] = t.Urn;
scrp.Script(urn);
When the program finishes running, the
schema output will be in a directory
hierarchy under the path specified by
the zBASEPATH identifier in the App.
Config file. By default, this path is set to
C:\SCHEMA.
A Final Warning
I need to give you a general warning about
the schema collection process using SMO:
The scripting process is not always fast. For
databases with hundreds or thousands of
tables and stored procedures in them, the
utility might take several hours to script
out all the schema entities. For this reason,
you might want to schedule the schema
collection program to run unattended. The
program writes its output to a log file (in
addition to the screen), and you can use the
log file to assess progress and watch for any
errors that may arise. I recommend using
the screen output to monitor progress
visually, but the screen output is optional
and does slow the schema scripting process a bit. So if you don't want to see the
screen output, you can simply comment
out the WriteStatus() function calls in the
C# code.
Finally, note that although I haven't done
so in the program, you might gain some
improved program speed if you use the
Server.SetDefaultInitFields() method when
you first set the Server entity. Although the
details are too involved for me to include
here, by default SMO doesn't retrieve every
property in its list of entities to be gathered,
for performance reasons. You can specify
which properties are included to prevent
needless round-trips to the server.
When you use the SchemaCollector program in your own environment, you can run
the program as needed or schedule it to run
periodically. In addition, you can easily extend
the program logic to handle multiple SQL
instances with a batch file or similar mechanism. Have fun using this utility to create your
own personalized schema collector.