SQL Server Magazine

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.

   Prev. page   [1] 2     next page