Listing tables in a database

Manohar Kamath
February 17, 1999

Contains: ASP, ADO

In this short article, we will see how to list tables in a database schema. This will help users to check if a table exists, before they can query the database. This is especially useful when using Access databases, which do not allow users to check a table's existence via SQL statements.

What is a database Schema?
A database schema, in a layman's terms, is simply a repository that has information about the structure and content of the database. It also contains information about how the data is stored internally, as well as how it is stored physically on the storage device.

Database schema is primary to any database, as all requests to any data go through it. Without a schema, it would be impossible to connect to any database. With that said, let's see how we can extract the list of tables from any database.

The preceding picture shows an Access 97 database with three tables. Access also maintains a database schema, like any other database. Our goal is to list these tables in our ASP code.

To connect to the database, we need to create a connection string or a DSN. Let's create a System DSN named "Library" that points to our library database.

The following ASP code depicts how we will extract the table names from the above database. While the above database is not a part of this tutorial, you can use almost any Access database to try the following code.

Before we proceed, I will tell you a little bit about the include file - adovbs.inc. This file ships with IIS/PWS, and is usually found in Program Files\Common Files\System\ADO directory. This file contains all the constants that are defined in the ADO library. You need to copy this file into your ASP directory. (We will be using VBscript in this tutorial. If you use JavaScript, use adojavas.inc instead)

<%@Language=VBScript %>
<!-- #include file="adovbs.inc"-->
<%
  ' Create a connection object
  Set Conn = Server.CreateObject ("ADODB.Connection")

  ' Open the connection to the database. I use a system DSN here, but
  ' you can use whatever method you wish
  Conn.Open "DSN=Library"

  ' Open the database schema to query the list of tables. Extract the
  ' list in a Recordset object
  Set Rs = Conn.OpenSchema (adSchemaTables)

  ' Loop through the list and print the table names
  Do While Not Rs.EOF
    Response.Write "<BR>" & Rs ("TABLE_NAME")
    Rs.MoveNext
  Loop

  ' Close and destroy the recordset and connection objects
  loRs.Close
  Set loRs = Nothing

  loConn.Close
  Set loConn = Nothing
%>

When you run the above code, you will see a result something like:

MSysACEs
MSysModules
MSysModules2
MSysObjects
MSysQueries
MSysRelationships
tBooks
tCheckoutHistory
tMembers

So, what are the list items highlighted in red? In fact, they are system tables, meaning tables maintained by Access for its own use. In fact, if you print out the Rs ("TABLE_TYPE") field, you can see that these tables are marked "SYSTEM TABLE" These tables list all the available objects within the database - tables, queries, modules etc. So, in a way they are tables that list tables.

Note: You can pass various other values to OpenSchema method to extract other information about the database. E.g. adSchemaColumns lists all the columns within the database. But for most purposes tables are important, and that's why we will deal only with them in this article.