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
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:
- ORDINAL_POSITION returns the serial number of the column within the table.
- TABLE_NAME contains the name of the table and appears in the query's WHERE clause.
- COLUMN_NAME contains the column name.
- IS_NULLABLE, with the possible values Yes or No, indicates whether the column will accept null values.
- DATA_TYPE contains the column's data type.
- CHARACTER_MAXIMUM_LENGTH contains the maximum length of the column in the case of character, binary, text, or image data. In the case of other data types, this column contains a NULL value. If the value of this column is NULL, NUMERIC_PRECISION identifies the size of the column.
- COLUMN_DEFAULT contains the column's default value (if any).
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