How to List the Related Tables in a Database

Summary:

Although Access allows you to create relationships among tables in
your database, there is no inherent command to list these
relationships. Sometimes this information can be of use, particularly
when trying to find the source table of a referential integrity
violation or other such example.

This article discusses how you can use a series of queries to display
a list of tables that are related in a database.

More Information:

Information on the relationships among the tables in a database can
sometimes be very helpful, particularly when trying to find the source
table of a referential integrity violation or other such example. This
information is also good for documenting your database.

Although Access allows you to create relationships among tables in
your database, there is no inherent command that will gather this
information and present it in a meaningful way. All of this
information can be made available by gathering and relating
information from the system tables MSysObjects, MSysColumns, and
MSysIndexes. This article discusses how you can create a query
containing two columns. The first column, called "Primary.Name," will
contain a table name. The second column, called "Foreign.Name," will
represent the table that the Primary.Name table is related to.

Because the information will be coming from the system tables
mentioned above, you must make sure you have access rights to these
tables. MSysObjects has these rights by default, so the instructions
below indicate how you can give yourself access rights to MSysIndexes
and MSysColumns.

1. In the Database Window menu, select the View menu, then choose
   Options.

2. Change the Show System Objects setting to Yes, then choose OK.

3. From the Database Window menu, choose the Permissions command from
   the Security menu.

4. In the following dialog box, choose Table for the Object Type
   prompt, then choose MSysIndexes for the Object Name prompt.

5. In the Permissions box at the bottom of the dialog, select the
   Read Data box.

6. Choose the Close button.

7. From the View menu at the top of the screen, choose Options.

8. Change Show System Objects to No, then choose the OK button.

At this point, you can begin building the queries that will retrieve
the relationship information. To do this, follow the instructions
below:

 1. In the database window, choose the Query button, then choose New.
    A query design screen appears, along with a dialog asking for a
    Table/Query to add to the query. Choose Close so that the query
    design grid is blank.

 2. From the View menu, choose SQL.

 3. Delete any contents that appear in the SQL dialog box.

 4. Enter the following into the SQL window:

       SELECT DISTINCTROW
          MSysObjects.Name, MSysObjects.Id
       FROM MSysObjects
       WHERE ((MSysObjects.Type=1)
       AND (MSysObjects.Flags Is Null Or MSysObjects.Flags<>2))
       ORDER BY MSysObjects.Name;

 5. Choose the OK button. Close and save the query as "psi User
    Tables."

 6. Repeat steps 1 through 4, substituting the SQL statement in step 4
    with the following SQL statement:

       SELECT DISTINCTROW [Tables].Name AS Table,
          MSysColumns.Name AS Field,
          [Tables].Id AS [Primary Id]
       FROM [psi User Tables] AS Tables, MSysColumns, Tables
       INNER JOIN MSysColumns ON [Tables].Id = MSysColumns.ObjectId
       ORDER BY [Tables].Name, MSysColumns.PresentationOrder;

 7. Choose the OK button, then close and save the query as "psi User
    Tables Field List."

 8. Repeat steps 1 through 4, substituting the SQL statement in step 4
    with the following SQL statement:

       SELECT DISTINCTROW Primary.Name, Foreign.Name
       FROM [psi User Tables] AS Primary, MSysIndexes, [psi User Tables]
          AS Foreign, MSysIndexes AS LKeyName, LKeyName
       INNER JOIN MSysIndexes ON LKeyName.Idxid = MSysIndexes.Idxid,
          LKeyName
       INNER JOIN MSysIndexes ON LKeyName.ObjectId = SysIndexes.ObjectId,
          Primary
       INNER JOIN MSysIndexes ON Primary.Id = MSysIndexes.ObjectId,
          Foreign INNER JOIN MSysIndexes ON Foreign.Id =
          SysIndexes.ObjectIdReference
       WHERE ((MSysIndexes.ObjectIdReference<>0)
          AND (MSysIndexes.Operation=2)
          AND (LKeyName.Operation=0))
       ORDER BY Primary.Name, Foreign.Name;

 9. Choose the OK button. Close and save the query as "psi
    Relationships."

10. To view the Relationships table, highlight the query called "psi
    Relationships" in the database window and choose the Open button.

With this query, you can create reports or use the query in any other
way that you would normally use an Access query to make use of the
information.