Listing tables in a database
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
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
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
<!-- #include file="adovbs.inc"-->
Set Conn = Server.CreateObject ("ADODB.Connection")
Set Rs = Conn.OpenSchema (adSchemaTables)
Do While Not Rs.EOF
Response.Write "<BR>" & Rs ("TABLE_NAME")
Set loRs = Nothing
Set loConn = Nothing
When you run the above code, you will see a result something
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.