When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles [1.x] [2.0]
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
Headlines from ASPWire.com
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML Info
Information:
Advertise
Feedback
Author an Article
Technology Jobs

















internet.com
IT
Developer
Internet News
Small Business
Personal Technology
International

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
Print this page.

Windows Systems Administrator
Jupitermedia
US-CT-Darien

Justtechjobs.com Post A Job | Post A Resume

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!

- continued -

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:

  • Download the Schema Code in Text Format
  • View the live demo!


    Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article



  • JupiterOnlineMedia

    internet.comearthweb.comDevx.commediabistro.comGraphics.com

    Search:

    Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

    Jupitermedia Corporate Info


    Legal Notices, Licensing, Reprints, & Permissions, Privacy Policy.

    Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers

    Solutions
    Whitepapers and eBooks
    Microsoft Article: HyperV-The Killer Feature in WinServer ‘08
    Avaya Article: How to Feed Data into the Avaya Event Processor
    Microsoft Article: Install What You Need with Win Server ‘08
    HP eBook: Putting the Green into IT
    Whitepaper: HP Integrated Citrix XenServer for HP ProLiant Servers
    Intel Go Parallel Portal: Interview with C++ Guru Herb Sutter, Part 1
    Intel Go Parallel Portal: Interview with C++ Guru Herb Sutter, Part 2--The Future of Concurrency
    Avaya Article: Setting Up a SIP A/S Development Environment
    IBM Article: How Cool Is Your Data Center?
    Microsoft Article: Managing Virtual Machines with Microsoft System Center
    HP eBook: Storage Networking , Part 1
    Microsoft Article: Solving Data Center Complexity with Microsoft System Center Configuration Manager 2007
    MORE WHITEPAPERS, EBOOKS, AND ARTICLES
    Webcasts
    Intel Video: Are Multi-core Processors Here to Stay?
    On-Demand Webcast: Five Virtualization Trends to Watch
    HP Video: Page Cost Calculator
    Intel Video: APIs for Parallel Programming
    HP Webcast: Storage Is Changing Fast - Be Ready or Be Left Behind
    Microsoft Silverlight Video: Creating Fading Controls with Expression Design and Expression Blend 2
    MORE WEBCASTS, PODCASTS, AND VIDEOS
    Downloads and eKits
    Sun Download: Solaris 8 Migration Assistant
    Sybase Download: SQL Anywhere Developer Edition
    Red Gate Download: SQL Backup Pro and free DBA Best Practices eBook
    Red Gate Download: SQL Compare Pro 6
    Iron Speed Designer Application Generator
    MORE DOWNLOADS, EKITS, AND FREE TRIALS
    Tutorials and Demos
    How-to-Article: Preparing for Hyper-Threading Technology and Dual Core Technology
    eTouch PDF: Conquering the Tyranny of E-Mail and Word Processors
    IBM Article: Collaborating in the High-Performance Workplace
    HP Demo: StorageWorks EVA4400
    Intel Featured Algorhythm: Intel Threading Building Blocks--The Pipeline Class
    Microsoft How-to Article: Get Going with Silverlight and Windows Live
    MORE TUTORIALS, DEMOS AND STEP-BY-STEP GUIDES