Listing table in a Database

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 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.

Sample Database The 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.

<%@Language=VBScript %>

<%
  ' 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
  Rs.Close
  Set Rs = Nothing

  Conn.Close
  Set Conn = 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..

asp access list tables access97