Have you ever wanted to be able to list your database's tables and columns on your intranet or Internet site? One option is to take a snapshot of your database design and post it to your Web. Or, using Schemas, you can have a live picture of your database on the Web!

To obtain a listing of the tables in a SQL Server database, you can use the sysobjects table. We discussed using this technique in an article entitled, " Using the sysobjects Table." However, what if you wanted to list the tables and columns in an Access database? Or a FoxPro database? Or an Oracle database? Or an Informix database? Or any ODBC-compliant database?

To retrieve database information from a wide array of data sources, you will need to use a schema. A schema is database information (in Recordset object format), from tables, to columns, to indexes, to constraints, and all sorts of other neat information.

To open a schema, you need to use the OpenSchema method of the Connection object. This returns a schema (in Recordset format), that contains certain information on the database that the Connection object points to. So, if you open the Schema adSchemaTables, you will receive information on the tables.

For more information on listing database system information, be sure to read: Programmatically Determining if a Table Exists and Determining the Creation Date of Tables!

Here is some code that will list all of the tables and columns in your database... Have fun with it!
Run this Example!

<%@ Language = VBSCRIPT %> <% Option Explicit %> <!--#include virtual="/adovbs.inc"--> <% 'Column variables Dim iLength, iPrecision, iScale, iDefaultValue Dim objConn, objTableRS, objColumnRS Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "DSN=4Guys" objConn.Open Set objTableRS = objConn.OpenSchema(adSchemaTables, _ Array(Empty, Empty, Empty, "TABLE")) Do While Not objTableRS.EOF Response.Write "<TABLE BORDER=1 CELLSPACING=1><TR>" Response.Write "<TH COLSPAN=7 BGCOLOR=#CCCCCC>" Response.Write "<FONT SIZE=+2>" Response.Write objTableRS("Table_Name").Value Response.Write "</FONT></TH></TR>" Response.Write "<TR><TD BGCOLOR=#CCCCCC>Name</TD>" Response.Write "<TD BGCOLOR=#CCCCCC>Datatype</TD>" Response.Write "<TD BGCOLOR=#CCCCCC>Length</TD>" Response.Write "<TD BGCOLOR=#CCCCCC>Precision</TD>" Response.Write "<TD BGCOLOR=#CCCCCC>Scale</TD>" Response.Write "<TD BGCOLOR=#CCCCCC>Allow Nulls</TD>" Response.Write "<TD BGCOLOR=#CCCCCC>Default Value</TD>" Response.Write "</TR>" Set objColumnRS = objConn.OpenSchema(adSchemaColumns, _ Array(Empty, Empty, objTableRS("Table_Name").Value)) Do While Not objColumnRS.EOF iLength = objColumnRS("Character_Maximum_Length") iPrecision = objColumnRS("Numeric_Precision") iScale = objColumnRS("Numeric_Scale") iDefaultValue = objColumnRS("Column_Default") If IsNull(iLength) then iLength = "&nbsp;" If IsNull(iPrecision) then iPrecision = "&nbsp;" If IsNull(iScale) then iScale = "&nbsp;" If IsNull(iDefaultValue) then iDefaultValue = "&nbsp;" Response.Write "<TR><TD>" & objColumnRS("Column_Name") & "</TD>" Response.Write "<TD>" & objColumnRS("Data_Type") & "</TD>" Response.Write "<TD>" & iLength & "</TD>" Response.Write "<TD>" & iPrecision & "</TD>" Response.Write "<TD>" & iScale & "</TD>" Response.Write "<TD ALIGN=CENTER><FORM><INPUT TYPE=CHECKBOX" If objColumnRS("Is_Nullable") then Response.Write " CHECKED" End If Response.Write "></FORM></TD>" Response.Write "<TD>" & iDefaultValue & "</TD>" Response.Write "</TR>" objColumnRS.MoveNext Loop objTableRS.MoveNext Set objColumnRS = Nothing Response.Write "</TABLE><P>" Loop objTableRS.Close Set objTableRS = Nothing objConn.Close Set objConn = Nothing %>

Attachments: