Have you ever had the need to script database schema for archiving purposes? Or
perhaps your boss has been bugging you to document your databases better to meet
the requirements of the Sarbanes-Oxley (SOX) Act. The SQL Management Options
(SMO) API in SQL Server 2005 makes it simple to script out tables, stored procedures,
views, user-defined functions (UDFs), and other schema objects within your databases.
This article describes a simple Windows console-mode utility called SchemaCollector,
written in C# using the .NET Framework, that you can use to collect database schema.
You specify a SQL Server instance to run the utility against as a command-line argument, and the schema collector program generates individual schema output files for
tables, stored procedures, views, functions, and roles in appropriate subdirectories within
a directory hierarchy. You can download the full source code
above.
Why Script Your Own Schema Collector?
You might be wondering why you might
want to write your own schema-collection
script when you could choose to purchase
a third-party tool to do the job. With many
companies nowadays being asked to "do
more with less," some database professionals
might find that it's more cost-effective to use
the powerful functionality that's built into
SQL Server. In addition, when a problem
arises or a change occurs in the environment, you can tweak your code and quickly
get back on track.
Of course, although generating an individual file for each database object might be
the most efficient method from a maintenance perspective, it would be burdensome
to recreate a large database from these files.
A typical production database—with, say,
500 tables—might generate thousands of
individual files by the time the schema generator is through. So you wouldn't use the
SchemaCollector utility for such a purpose.
Instead, you would use this program for specific, limited purposes. For example, in my
company, I've used the utility for tracking
schema changes over time and publishing
production schema for developers.
Tracking schema changes over time. In
your company (like mine) you might be
under pressure to carefully track database
changes over time to comply with SOX
and other regulatory initiatives. In a large
company that has many groups working on various projects, pinpointing the exact date,
time, and root cause of database problems can
be difficult. You can use the SchemaCollector
utility to check the schema output files into
Visual SourceSafe (or an equivalent source-code repository system) to allow analysis of
schema from a change perspective. Then, you
can use diff tools and other features built into
Visual SourceSafe to determine exactly when
schema changes occurred, where schema
changes occurred, and why things broke.
The TABLES, PROCEDURES, VIEWS,
FUNCTIONS, ROLES directory structure that the SchemaCollector utility uses
lends itself nicely to source-code repository
maintenance.
Publishing production schema for developers. Some companies have a policy that
requires that programmers be completely
locked out of production. In my company,
the data we manage involves sensitive medical
and personnel information, so we can't give
developers the db_datareader permission.
And because it's an administrative hassle in
SQL Server 2000, we don't even try to give
them rights to view schema. As a result, development is totally in the dark about what the
production schema looks like.
Over time, as programmers "dirty up"
their development environment, their
schema gets out of sync with production.
So programmers must frequently ask me
to email the production definitions for a
handful of tables or stored procedures so
that they can bring their development environment back into sync with production.
To solve this problem, I use the SchemaCollector utility to routinely capture our
production schema across all servers and all
databases and publish the schema to a central
directory share. I created an Intranet Web
viewer application that lets developers view
the production schema on any production
server or database at will. I can run the
SchemaCollector in an ad-hoc fashion from
the command line to collect schema from all
databases on one SQL Server instance, or I
can schedule the utility to run periodically
(e.g., under the Windows Task Scheduler)
and collect schema for multiple systems
weekly or monthly.
Let's Get Started
The C# source-code files for the SchemaCollector utility are in the downloadable .zip
file at InstantDoc ID 94510. The Program.cs
file contains the main application code, App.
Config is the application configuration file,
and WriteLog.cs contains a C# class that's
used for logging data to a text file. First, to
make calls to SMO API functions, we need
to include the following directive at the top
of our file:
using Microsoft.SqlServer.
Management.Smo;
Next, we need to add some references
in our .NET project so that the SMO
DLLs can be included in the Visual Studio
project. Before we can write code against an
external component (e.g., a .NET Framework component, a COM component,
an assembly or class library, an XML Web
Service), our project first needs to contain
a reference to that component, as Figure 1 shows. To add a reference in your project,
from the main Visual Studio menu, select
Project, Add Reference. Alternatively, you
can right-click the name of the project in
the Solution Explorer and select Add Reference. You'll see a dialog box that looks like
the one in Figure 2.
For the purposes of this project, you need
three SMO references: Microsoft.SqlServer.
ConnectionInfo, Microsoft.SqlServer.Smo,
and Microsoft.SqlServer.SmoEnum. The
Microsoft.SqlServer.ConnectionInfo DLL
contains some methods for parsing connection strings and accessing properties of
connections. The Microsoft.SqlServer.Smo
DLL contains the real guts of SMO and
includes instance and utility classes that let
users manipulate SQL Server programmatically. The Microsoft.SqlServer.SmoEnum
DLL contains some classes that make it easy
to enumerate (or "walk through") all SMO
objects, as you'd do with a collection. These
DLL files reside in the Microsoft Visual
Studio path, typically in the C:\Program
Files\Microsoft SQL Server\90\SDKAssemblies folder.
You'll also want to add a reference to
the System.configuration class, which lets
the program read configuration information
from the App.config file. The App.Config
configuration file is a good place to store
entities that can change over time, such as
database connection strings. In our case, we
use the configuration file to maintain the base path to the location where schema files
will be on the file system.
Prev. page
[1]
2
3
next page 
