Keep Database Schema Online

Build an ASP page that lets you access schema anytime

The development or testing team for a Web-based database application often needs to document the current database schema. If the Web application is hosted on a remote server, the team might enjoy being able to access the current schema by simply typing a URL in a browser, without having to use Enterprise Manager, Microsoft Visual InterDev, Visual Basic (VB), or other tools. With the approach we describe, you can use the HTTP protocol on port 80 to get all the schema details through the Internet. Other tools require a connection on a different IP port, and most corporate firewalls disallow communication through other ports.

This article helps you build an Active Server Pages (ASP) page, which you can use anytime, anywhere to get the latest schema in today's most accepted file format: HTML. So, whenever a development team member changes any part of the schema, the page will reflect the changes at the next access, even if the time gap between the change and the access is as short as a second. Because this page becomes a part of your Web-based database project and thus is available on your development Web server, the document is available instantly to all project participants who need the schema.

This article also demonstrates an important new feature of SQL Server 7.0—information schema views—and provides an example of how ADO, SQL Server 7.0, VBScript, Cascading Style Sheets (CSS), and Dynamic HTML (DHTML) integrate seamlessly. HTML, DHTML, and CSS form the presentation layer, and VBScript acts as near-perfect glue, connecting the HTML layer and the data layer.

SQL Server 7.0 Diagrams

The diagram feature of SQL Server 7.0, which you can access through Enterprise Manager, makes visual database design easy. Every SQL Server database has a diagram option among the options you see when you expand the node that shows the database name. You can have any number of diagrams, so you don't need to combine all the information into unmanageable, complex diagrams.

Why do we mention the SQL Server diagram feature here? You can use information schema views to add to the diagram feature to make it more fluid and let your information flow across time zones and continents seamlessly through the Internet. But the diagram feature has drawbacks. The most important problem with SQL Server diagrams is that you can't save diagrams separately from SQL Server, let alone save them in a common file format, so sending and receiving diagrams can be difficult. Also, if you print the diagram and you have the column properties set to on, you might end up with a book because a table or two can consume a whole page. Another option is to forego the column properties, as Figure 1, page 56, shows, but the recipients would need to interpret the properties, which can lead to errors and misunderstandings. Of course, one unattractive alternative remains—you could back up the entire database and email it.

Life Before SQL Server 7.0

Meta data is the set of attributes that help you retrieve information about or describe a database's structure—for example, the names of tables, their columns, primary keys, foreign keys, or rules applicable to columns. Meta data doesn't contain any operational data; SQL Server uses meta data to document the way the database is constructed. In SQL Server 6.5 and earlier, you might execute a system stored procedure such as sp_help to view meta data. Or you could query the system tables directly. For example, if you want a list of all user-created tables in the current database, you can execute the following query:

SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name

The sysobjects system table contains one row for each object (e.g., constraint, default, log, rule, stored procedure) in a database. In the above query, type = 'U' retrieves the names of all user-created tables.

Getting the Database Structure with SQL Server 7.0

SQL Server 7.0 provides yet another method of obtaining meta data: querying information schema views. The definitions of the views are in a special schema named INFORMATION_SCHEMA, which each database contains. These views conform to the SQL-92 standard definition of the information schema. SQL Server Books Online (BOL) advises that to obtain meta data, you use only system stored procedures or these INFORMATION_SCHEMA views. Querying the system tables directly might not provide accurate information if Microsoft changes the system tables in future releases.

The main advantage of SQL Server 7.0 information schema views comes directly from the benefits of a typical view. Namely, the abstraction layer hides the underlying complexity of the database's system information (meta data). INFORMATION_SCHEMA helps you obtain meta data in a simple way; for instance, you can use

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 

instead of

SELECT name FROM sysobjects WHERE type  = 'U'

You can easily obtain the meta data from INFORMATION_ SCHEMA views, as the first of these queries shows. You can find all the information schema views in Enterprise Manager under Server Group, Server, Databases, Database, Views. The best aspect of information schema views is that you can explore them to find out how and from which source the view is gathering data. Right-clicking an item you're interested in and selecting Properties will expose a screen like the one Figure 2 shows.

The views in Table 1 contain meta data for all objects stored in a particular database. In this example, we'll develop an ASP page that uses these views to obtain information about the meta data. To keep the discussion simple, we're restricting this example to documenting the table structure, the default values (if any) of the columns, and the constraints (foreign keys, primary keys, and check constraints). In the ASP code, we'll use only the TABLES, COLUMNS, TABLE_CONSTRAINTS, CHECK_CONSTRAINTS, and KEY_COLUMN_USAGE views to generate the document. Executing this article's combined code against the Pubs database produces an HTML page in your browser.

Let's delve into the process of creating the ASP page. To make the code more readable, we avoid mixing HTML and ASP code. Listing 1 sets up the basic structure of an HTML document and passes the control to the function Main.

The function Main in Listing 2, page 58, expects to receive a query string called strConnectString that contains the required database connection string. If the code doesn't supply the connection string, the function Main tries to connect to the local SQL Server machine's Pubs database. The function Main then uses the ADO Connection object to connect to the database and executes a query on the INFORMATION_SCHEMA.TABLES view.

The INFORMATION_SCHEMA.TABLES view returns four columns: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and TABLE_TYPE. TABLE_CATALOG is the ANSI-92 equivalent of a database name in SQL Server. If you're running this script against the Pubs database, this column will contain a single value, PUBS, for all rows. TABLE_SCHEMA is the ANSI-92 equivalent of an owner name in SQL Server and contains values such as DBO. TABLE_NAME contains the names of the tables or views in the database. TABLE_TYPE can have two possible values: VIEW for views or BASE TABLE for tables.

We're restricting this example to the table list, so we've included the TABLE_TYPE column in the WHERE clause. The query returns an ADO Recordset object, then loops through this recordset and calls two procedures. The first procedure renders the table structure, and the second procedure renders the constraints for that table. Both procedures accept the table name and the connection object as the parameters.

Web Listing 1 contains the code for a procedure called RenderTableAttributes. (You can find Web Listing 1 by entering Instant Doc ID 15456 at http://www.sqlmag.com/ and clicking Download the code in the Article Information box.) This procedure accepts the table name and the connection objects as the parameters. Using the parameters, the code queries the INFORMATION_SCHEMA.COLUMNS view, which contains one row for each column accessible to the current user in the current database. This view has 23 columns that contain all possible column attributes, such as database name, table name, data type, precision, and nullability. In this example, we're using only the following seven columns:

The code queries the INFORMATION_SCHEMA.COLUMNS view for the passed table name. Then the code uses the Response object's Write method to begin rendering HTML to the browser. The code renders the table name with a font size larger than that of the contents, then it uses the HTML tag to render an HTML table. The code loops through the recordset and outputs the values of the aforementioned seven columns.

After the table structure is in place, use Web Listing 2 to create another table that contains the information about the constraints (primary key, foreign key, and check constraints) that pertain to the first table. The code queries the INFORMATION_SCHEMA .TABLE_CONSTRAINTS view, passing the table name in the WHERE clause. The view has nine columns containing values such as constraint owner, table owner, and so on, but we'll use only the TABLE_NAME, CONSTRAINT_NAME, and CONSTRAINT_TYPE columns to get the required values. TABLE_NAME contains the name of the table for which the constraint has been defined. CONSTRAINT _NAME contains the name of the constraint. CONSTRAINT_TYPE, which defines the type of constraint, has four possible values: CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY.

The code checks the constraint type and calls two procedures to return the constraint's value. The first procedure, GetClauseForConstraint, is called if the type of constraint is a check condition. This procedure returns the check clause for that constraint. The procedure RenderTableConstraints passes the constraint name and the connection object as the parameters. The function then queries the INFORMATION_SCHEMA.CHECK_ CONSTRAINTS view to get the check constraint and returns this information to the calling function. This view has four columns containing the database name, table name, and so forth, but we need only the CHECK_CLAUSE column to get the text of the check constraint.

The RenderTableConstraints procedure retrieves information about the remaining constraints—PRIMARY KEY, FOREIGN KEY, or UNIQUE—from the function GetColumnsForConstraint, which queries the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view. Because these constraints can have more than one column, you'll get multiple rows for the constraint. This view has eight columns containing constraint name and owner, table name and owner, and so on. We need two columns, COLUMN_NAME and ORDINAL_POSITION, from this view to serve our purpose.

The query returns one row for each column involved in the constraint. Then the query loops through the recordset and constructs a string containing all the columns, which represents the constraint.

Universal Access

Now you've built an ASP page that you can copy to any project. By simply passing the connection string as a parameter to the Web page, you can get the entire structure of the database formatted as HTML, which is available to all types of browsers. With proper security restrictions in place, you can deploy this Web page in any of your Web projects. Then your developers, DBAs, testing team, or users can view the current structure of your underlying database over the Internet, without needing tools such as Enterprise Manager to connect directly to the database. Information schema views give you all possible information about the database. And you can further expand this example to meet your project's specific requirements.

The page results travel over HTTP, which makes the page universal and deployment-free. Armed with the power of information schema views, Visual Basic for Applications (VBA), and a couple of additional tables to store extra information (e.g., table description or column description), you can create the project's back-end documentation in Microsoft Word or Microsoft Excel format.

You might wonder how much effort is required to ensure this kind of continuous schema availability. Maintaining the availability requires no ongoing effort. You simply copy the ASP page we explain in this article to your Microsoft IIS Web server directory and adjust the connection string or Data Source Name (DSN) configuration as necessary. You can copy the page to the INETPUBWWWROOT directory and set the connection string to use SQL Server's Pubs sample database. Then just type the URL into your browser as http://web-server name or IP address/dbschema.asp to see your schema online, as Figure 3 shows.