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 <TABLE> 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 INETPUB\WWWROOT 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.