Re-Create SQL Server Databases with VB and SQL-DMO

Contributing author Bob Hyland presents his SQLScript utility

[Editor's Note: VB Solutions is about using Visual Basic (VB) to build a variety ofsolutions to specific business problems. This column doesn't teach you how to write VB, but how touse 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 tore-create the database schema (or layout) with Transact-SQL scripts. For example, you mightuse a Transact-SQL script to re-create a blank database at a customer's site, or you might want toinclude the database definition in documentation. This month's VB Solutions column, written by BobHyland, presents the SQLScript utility, which examines a SQL Server database and generates theTransact-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 writeall changes to a database object to a text file. The text file contains a Transact-SQL script thatdeletes the object and then re-creates it. You first change the SQL script file and then run thescript, 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, againusing ISQL or a similar utility. At regular intervals, you reverse-engineer the definition of thedatabase 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 Serveruses these definitions to assemble objects as it accesses them. For instance, to access a databasetable, SQL Server needs to know which columns to combine, the data types of the columns, thesequence of the columns, and so forth. Creating a script by reverse-engineering the system tablesrequires detailed knowledge about how the system tables store information and how to traverse thesystem tables to build a SQL CREATE statement for each object. To make matters worse, differentsystem tables store the definitions of different types of objects, and object dependencies andordering the CREATE statements further complicate the process.

Enter the SQLScript utility. SQLScript uses reverse- engineering to save a database schema to atext file at regular intervals. SQLScript uses SQL Server's SQL-DMO object library and lets me takea snapshot of the database schema on demand.

As Mike Otey described in the December 1996 VB Solutions column, "Managing SQL Server withVB," SQL-DMO is a 32-bit Object Linking and Embedding (OLE) library defined in the SQLOLE32.TLBfile and implemented in SQLOLE32.DLL. Distributed with SQL Server, SQL-DMO provides a library ofhigh-level objects that model the database objects in SQL Server. SQL-DMO returns information abouthow best to traverse system tables to learn a database object's definition and information about therelationships between the database objects, including object dependencies, ownership, andpermissions.

With SQL Server, as with other relational database management systems (RDBMSs) the order inwhich you create objects is important; some objects depend on the existence of other objects. Forexample, if you have a SQL Server view named EmployeesView that's based on your Employees databasetable, Employees must exist before you can compile the script to create EmployeesView. Similarly, ifyou have a stored procedure named ShowDepartment, which receives a company department number as aparameter and lists only employees from EmployeesView in that department, EmployeesView (andEmployees) must exist before you can create the stored procedure. SQL-DMO includes the DatabaseEnumDependencies method, which you can use to determine the proper database object creation order touse in your database creation script.

How to Use SQLScript
You can use SQLScript in two modes: graphical mode and command-line mode. This column willexamine the graphical mode. Running SQLScript in graphical mode presents the Connect to SQL Serverwindow 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 thisinformation to connect a SQL-DMO SQLServer object to the server. The program then uses SQL-DMO topopulate a drop-down combo box of the databases that reside on the server. When you click a databasename, SQL-DMO creates a Database object and uses the EnumDependencies method to return a list ofdatabase objects in proper creation order.

As SQLScript encounters each object, it creates a reference to a SQL-DMO object of the samedatabase type and uses the object's Script method to get the CREATE statement for that object. Theprogram combines the scripts in sequence in a list box. After the program finishes with all theobjects, the list box contains the Transact-SQL script--complete with dependencies--for the selecteddatabase.

Screen 2 shows an example SQL Script window that depicts this process. At the top of the windowis the drop-down combo box from which you select a database. To its right is a text box where youcan enter the name of a file to write the script to. (The script appears in the list box in themiddle of the window.) Clicking Save As invokes the File, Save As dialog box and writes the scriptto the file (if you don't cancel).

Inside SQLScript
The StartUp form for the Visual Basic (VB) project is SQLScript's Main subroutine. In Main, theabsence or presence of a command-line string returned by the VB function Command$() determineswhether you are using the graphical mode or the command-line mode. If Command$() returns a commandline, the program parses the command line for correct syntax and the program runs in batch mode;otherwise, the program runs in graphical mode.

When you are using SQL-DMO, you first must create and connect an instance of a SQL-DMOSQLServer object. After SQLScript displays the Connect to SQL Server window and you enter the servername, username, and password and click OK, SQLScript creates the SQLServer object and attempts toconnect to the chosen server. (I borrowed a subroutine from the SQL Export utility that Mike Oteypresented in his January 1997 VB Solutions column to load the list of available SQL Servers on thenetwork into the drop-down combo box in the Connect to SQL Server window.) The global variable,goSQL, stores the reference to the SQLServer object for later use.

After connecting to the server, SQLScript loads the SQL Script window to let you browse thedatabases on the server. While the window is loading, SQLScript cycles through the server Databasescollection, adding the name of each database to the drop-down combo box. Callout A in Listing 1shows the code that generates this list of databases.

You do not need to explicitly create a new SQL-DMO Database object to get the value of the Nameproperty. An OLE collection has the Item method as a default property. In Listing 1, the Itemreturned is the numeric index of the Databases collection item, iterated by the subscript i.

After you choose a database from the drop-down combo box, the program fires the combo box Clickevent and uses the Database EnumDependencies method to build the database creation script. At A inListing 2, goDB stores the reference to the SQL-DMO Database object of the database you selected.

To re-create a database, the script must define all the database objects. Therefore, theprogram must be able to process all database object types: SQL Server rules, defaults, anduser-defined data types (UDDTs), as well as tables, views, and stored procedures, which can involvedependencies. The EnumDependencies method takes a parameter that specifies the depth to which itwill build dependencies. To get all dependencies, SQLScript uses a SQL-DMO constant,SQLOLEDep_FullHierarchy, in the call to the Enum-Dependencies method at A in Listing 2.

Logically, defining rules, defaults, and UDDTs first makes sense because they do not depend onother user objects for their creation. Alternatively, a table can have defaults and rules bound toits columns, a view can depend on that table, and so on. EnumDependencies returns the correct objectdefinition order in just that fashion, with up to three separate result sets: the first set forrules and defaults, the second set for UDDTs, and the third set for tables, views, and storedprocedures in the database in the proper sequence.

With some VB data access methods, multiple result sets can be a problem. Fortunately,EnumDependencies returns a SQL-DMO QueryResults object. The QueryResults object has a ResultSetsproperty, which specifies the number of result sets returned, and a CurrentResultSet property, whichlets you select a particular result set for processing. The code at B in Listing 2 processes all theresult sets returned by the EnumDependencies method.

We also need to use the Columns property of the QueryResults object, which specifies the numberof columns in the current result set, because result sets can vary in the number of columns theyreturn. All three result sets return four common columns: the object type, the name, the owner, anda sequence number that indicates which pass the object needs to be created in. Each passcreates objects whose dependencies have been met in the previous pass, until the whole dependencytree has been created. The third result set of tables, views, and stored procedures has threeadditional columns that pertain to dependent objects: the relationship type, the name, and the ownerof the related object.

If an object depends on other objects (e.g., a view that joins two tables), the result setcontains a row for each dependent object (each table). Conversely, if several objects depend on aparticular object (e.g., several views include a particular table), the result set also containsrows for each dependency that object (the table) participates in. When obtaining the script for eachobject in the creation sequence, you want to create an object only once; therefore, you need to makesure no duplicate objects exist in the object definition list.

The Object Dependency List box you see in Screen 2, contains the information returnedby EnumDependencies. The GetRangeString method of a QueryResults object returns the rows and columnsof the current result set in tab-delimited columns and line feed-delimited rows; a double line feedseparates result sets. The code at C in Listing 2 stores the result set in a string matrix.

To prevent duplicate objects from appearing in the script, you need to uniquely identify eachobject, regardless of its type. You can use the object type, object owner, and object name columnsreturned in the result sets for identification. You must return each row of the string matrix; checkthe type, name, and owner of the object that row defines; and build the unique name. The code at Din Listing 2 shows how to use the Rows property and the GetColumnxxx methods of QueryResultsto complete this process. At E in Listing 2, the Select statement converts each object type to astring constant and fully qualifies the object name.

SQLScript uses a list box and a Windows API call to ensure that it saves only one reference toeach object. At F in Listing 2, the code uses the SendMessage API call with anLB_FINDSTRING message to determine whether the list box already contains that string. If the stringis in the list box, the program skips that row of the string matrix; otherwise, the program adds thestring to the list box. This process prevents duplicate objects from being added to the databasecreation script.

You need to add the object's creation script to the database script at the same time you updatethe list box. To add the object's script, you need to know its object type. The SQL-DMO Databaseobject has collections for all object types, which you can reference like other collections, but youneed to know which collection to retrieve each object from. (Figure 1 shows the hierarchy forSQL-DMO objects.) Each collection returns a different object type: For example, the Table collectionreturns a SQL-DMO table. Each object type has a Script method that returns the CREATE statement forthat object. The Script method takes a flag parameter that specifies what attributes of the objectyou want returned in the script. For example, for a rule, you want only the primary object and itsscript. For a table, you also want any bindings, indexes, user permissions, and so forth. In theprogram, the constants TABLE_SCRIPT_FLAGS, VIEW_SCRIPT_FLAGS, and SP_SCRIPT_FLAGS are concatenationsof the appropriate flags for each object type. The code at G in Listing 2, creates areference to a SQL-DMO object of the same type as the database object and adds the object's scriptto the database script.

After SQLScript cycles through all the result sets, it displays the concatenated scripts of thedatabase objects in the Database Object Creation Script box shown in Screen 2. The resultingTransact-SQL statement defines all the objects in the database in proper order.

Anomalies
You need to be aware of a few anomalies about SQLScript. First, you can use the script topopulate a new database. However, if you define rules, defaults, and UDDTs in your modeldatabase and the script also has CREATE statements for those objects, you will get errors becauseyou are defining a duplicate name.

Second, if you get a message that states "Cannot add rows to Sysdepends for the currentstored procedure because it depends...," you probably need to recompile the stored procedurethat generates the error. This message means that the stored procedure depends on one or moreobjects that have been altered and recompiled since the last time you compiled the stored procedure.Recompiling the stored procedure reestablishes the dependency that SQL Server needs.

Finally, SQLScript does not create any scripts to add SQL Server logins or create databaseusers. The object scripts are owner qualified, so if a user does not exist in the database where youapply the script, that CREATE will fail.

Rich Toolset
SQL-DMO is a rich set of OLE tools for SQL Server users and a good example of how an OLE librarycan implement the black-box features of object-oriented development. I did not need to know anythingabout how to traverse SQL Server system tables to define object dependencies--EnumDependenciesprovides that knowledge. I needed only to know about SQL-DMO and be willing to learn how to use it.SQLScript is one example of what SQL-DMO has to offer. The utility lets me reverse-engineer mydatabases and dump the Transact-SQL creation scripts to disk files. With SQLScript, I can takeon-demand snapshots of my SQL Server databases and use the snapshots for tasks such as making ad hocchanges during the day and letting my NT scheduler regenerate my database script overnight.