\[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).
The StartUp form for the Visual Basic (VB) project is SQLScript's Main subroutine. In Main, the absence or presence of a command-line string returned by the VB function Command$() determines whether you are using the graphical mode or the command-line mode. If Command$() returns a command line, 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-DMO SQLServer object. After SQLScript displays the Connect to SQL Server window and you enter the server name, username, and password and click OK, SQLScript creates the SQLServer object and attempts to connect to the chosen server. (I borrowed a subroutine from the SQL Export utility that Mike Otey presented in his January 1997 VB Solutions column to load the list of available SQL Servers on the network 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 the databases on the server. While the window is loading, SQLScript cycles through the server Databases collection, adding the name of each database to the drop-down combo box. Callout A in Listing 1 shows 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 Name property. An OLE collection has the Item method as a default property. In Listing 1, the Item returned 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 Click event and uses the Database EnumDependencies method to build the database creation script. At A in Listing 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, the program must be able to process all database object types: SQL Server rules, defaults, and user-defined data types (UDDTs), as well as tables, views, and stored procedures, which can involve dependencies. The EnumDependencies method takes a parameter that specifies the depth to which it will 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 on other user objects for their creation. Alternatively, a table can have defaults and rules bound to its columns, a view can depend on that table, and so on. EnumDependencies returns the correct object definition order in just that fashion, with up to three separate result sets: the first set for rules and defaults, the second set for UDDTs, and the third set for tables, views, and stored procedures 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 ResultSets property, which specifies the number of result sets returned, and a CurrentResultSet property, which lets you select a particular result set for processing. The code at B in Listing 2 processes all the result sets returned by the EnumDependencies method.
We also need to use the Columns property of the QueryResults object, which specifies the number of columns in the current result set, because result sets can vary in the number of columns they return. All three result sets return four common columns: the object type, the name, the owner, and a sequence number that indicates which pass the object needs to be created in. Each pass creates objects whose dependencies have been met in the previous pass, until the whole dependency tree has been created. The third result set of tables, views, and stored procedures has three additional columns that pertain to dependent objects: the relationship type, the name, and the owner of the related object.
If an object depends on other objects (e.g., a view that joins two tables), the result set contains a row for each dependent object (each table). Conversely, if several objects depend on a particular object (e.g., several views include a particular table), the result set also contains rows for each dependency that object (the table) participates in. When obtaining the script for each object in the creation sequence, you want to create an object only once; therefore, you need to make sure no duplicate objects exist in the object definition list.
The Object Dependency List box you see in Screen 2, contains the information returned by EnumDependencies. The GetRangeString method of a QueryResults object returns the rows and columns of the current result set in tab-delimited columns and line feed-delimited rows; a double line feed separates 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 each object, regardless of its type. You can use the object type, object owner, and object name columns returned in the result sets for identification. You must return each row of the string matrix; check the type, name, and owner of the object that row defines; and build the unique name. The code at D in Listing 2 shows how to use the Rows property and the GetColumnxxx methods of QueryResults to complete this process. At E in Listing 2, the Select statement converts each object type to a string 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 to each object. At F in Listing 2, the code uses the SendMessage API call with an LB_FINDSTRING message to determine whether the list box already contains that string. If the string is in the list box, the program skips that row of the string matrix; otherwise, the program adds the string to the list box. This process prevents duplicate objects from being added to the database creation script.
You need to add the object's creation script to the database script at the same time you update the list box. To add the object's script, you need to know its object type. The SQL-DMO Database object has collections for all object types, which you can reference like other collections, but you need to know which collection to retrieve each object from. (Figure 1 shows the hierarchy for SQL-DMO objects.) Each collection returns a different object type: For example, the Table collection returns a SQL-DMO table. Each object type has a Script method that returns the CREATE statement for that object. The Script method takes a flag parameter that specifies what attributes of the object you want returned in the script. For example, for a rule, you want only the primary object and its script. For a table, you also want any bindings, indexes, user permissions, and so forth. In the program, the constants TABLE_SCRIPT_FLAGS, VIEW_SCRIPT_FLAGS, and SP_SCRIPT_FLAGS are concatenations of the appropriate flags for each object type. The code at G in Listing 2, creates a reference to a SQL-DMO object of the same type as the database object and adds the object's script to the database script.
After SQLScript cycles through all the result sets, it displays the concatenated scripts of the database objects in the Database Object Creation Script box shown in Screen 2. The resulting Transact-SQL statement defines all the objects in the database in proper order.
You need to be aware of a few anomalies about SQLScript. First, you can use the script to populate a new database. However, if you define rules, defaults, and UDDTs in your model database and the script also has CREATE statements for those objects, you will get errors because you are defining a duplicate name.
Second, if you get a message that states "Cannot add rows to Sysdepends for the current stored procedure because it depends...," you probably need to recompile the stored procedure that generates the error. This message means that the stored procedure depends on one or more objects 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 database users. The object scripts are owner qualified, so if a user does not exist in the database where you apply the script, that CREATE will fail.
SQL-DMO is a rich set of OLE tools for SQL Server users and a good example of how an OLE library can implement the black-box features of object-oriented development. I did not need to know anything about how to traverse SQL Server system tables to define object dependencies--EnumDependencies provides 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 my databases and dump the Transact-SQL creation scripts to disk files. With SQLScript, I can take on-demand snapshots of my SQL Server databases and use the snapshots for tasks such as making ad hoc changes during the day and letting my NT scheduler regenerate my database script overnight.