Contributing author Bob Hyland presents his SQLScript utility
[Editor's Note: VB Solutions is about using Visual Basic (VB) to build a variety of
solutions to specific business problems. This column doesn't teach you how to write VB, but how to
use VB as a tool to provide quick, easy-to-implement solutions that you can use right away.]
In software development, when you change SQL Server database objects, you need to be able to
re-create the database schema (or layout) with Transact-SQL scripts. For example, you might
use a Transact-SQL script to re-create a blank database at a customer's site, or you might want to
include the database definition in documentation. This month's VB Solutions column, written by Bob
Hyland, presents the SQLScript utility, which examines a SQL Server database and generates the
Transact-SQL script that re-creates it.
Storing the Transact-SQL Script
The two common methods for storing the Transact-SQL script that creates the database schema are:
the text file method and the reverse-engineering method. In the text file method, you first write
all changes to a database object to a text file. The text file contains a Transact-SQL script that
deletes the object and then re-creates it. You first change the SQL script file and then run the
script, using SQL Server's Interactive SQL (ISQL) utility or SQL Enterprise Manager's Query Window.
In the reverse-engineering method, you make changes to the SQL Server database as needed, again
using ISQL or a similar utility. At regular intervals, you reverse-engineer the definition of the
database to create a Transact-SQL script that you can use to re-create the database objects.
SQL Server system tables store the information that defines the database objects. SQL Server
uses these definitions to assemble objects as it accesses them. For instance, to access a database
table, SQL Server needs to know which columns to combine, the data types of the columns, the
sequence of the columns, and so forth. Creating a script by reverse-engineering the system tables
requires detailed knowledge about how the system tables store information and how to traverse the
system tables to build a SQL CREATE statement for each object. To make matters worse, different
system tables store the definitions of different types of objects, and object dependencies and
ordering the CREATE statements further complicate the process.
Enter the SQLScript utility. SQLScript uses reverse- engineering to save a database schema to a
text file at regular intervals. SQLScript uses SQL Server's SQL-DMO object library and lets me take
a snapshot of the database schema on demand.
As Mike Otey described in the December 1996 VB Solutions column, "Managing SQL Server with
VB," SQL-DMO is a 32-bit Object Linking and Embedding (OLE) library defined in the SQLOLE32.TLB
file and implemented in SQLOLE32.DLL. Distributed with SQL Server, SQL-DMO provides a library of
high-level objects that model the database objects in SQL Server. SQL-DMO returns information about
how best to traverse system tables to learn a database object's definition and information about the
relationships between the database objects, including object dependencies, ownership, and
permissions.
With SQL Server, as with other relational database management systems (RDBMSs) the order in
which you create objects is important; some objects depend on the existence of other objects. For
example, if you have a SQL Server view named EmployeesView that's based on your Employees database
table, Employees must exist before you can compile the script to create EmployeesView. Similarly, if
you have a stored procedure named ShowDepartment, which receives a company department number as a
parameter and lists only employees from EmployeesView in that department, EmployeesView (and
Employees) must exist before you can create the stored procedure. SQL-DMO includes the Database
EnumDependencies method, which you can use to determine the proper database object creation order to
use in your database creation script.
How to Use SQLScript
You can use SQLScript in two modes: graphical mode and command-line mode. This column will
examine the graphical mode. Running SQLScript in graphical mode presents the Connect to SQL Server
window you see in Screen 1. The Server drop-down combo box lists the SQL Servers on your network.
You select a SQL Server, enter your username and password, and click OK. SQLScript uses this
information to connect a SQL-DMO SQLServer object to the server. The program then uses SQL-DMO to
populate a drop-down combo box of the databases that reside on the server. When you click a database
name, SQL-DMO creates a Database object and uses the EnumDependencies method to return a list of
database objects in proper creation order.
As SQLScript encounters each object, it creates a reference to a SQL-DMO object of the same
database type and uses the object's Script method to get the CREATE statement for that object. The
program combines the scripts in sequence in a list box. After the program finishes with all the
objects, the list box contains the Transact-SQL script--complete with dependencies--for the selected
database.
Screen 2 shows an example SQL Script window that depicts this process. At the top of the window
is the drop-down combo box from which you select a database. To its right is a text box where you
can enter the name of a file to write the script to. (The script appears in the list box in the
middle of the window.) Clicking Save As invokes the File, Save As dialog box and writes the script
to the file (if you don't cancel).
Prev. page
[1]
2
3
next page 
