Published: Sunday, October 17, 1999
Listing the Tables and Columns in a Database
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.
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"-->
<%
<![CDATA[
'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 = " "
If IsNull(iPrecision) then iPrecision = " "
If IsNull(iScale) then iScale = " "
If IsNull(iDefaultValue) then iDefaultValue = " "
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:
Download the Schema Code in Text Format
View the live demo!
