Query to obtain Schema and other info about the DB tables

  • Thread starter Thread starter JohnS
  • Start date Start date
J

JohnS

Is there a way to query Access (from within Access) to get a list of the
tables and queries in the DB?

I am trying to document a DB and would like to list out the table names and
get counts of tables without counting them by hand and retyping the names by
hand.

TIA
 
The following query will give you the tables:

SELECT [Name]
FROM MSysObjects
WHERE Name Not Like "MSys*"
AND [Type] In (1,4,6)
ORDER BY [Name]

(type 1 means tables in the same database, type 4 means tables linked using
ODBC, type 6 means other linked tables).

The following query will give you the queries:

SELECT [Name]
FROM MSysObjects
WHERE [Type] =5
ORDER BY [Name]

To avoid reinventing the wheel, you might check what Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/doctable.html
 
Thanks.
Exactly what I was looking for.



Douglas J. Steele said:
The following query will give you the tables:

SELECT [Name]
FROM MSysObjects
WHERE Name Not Like "MSys*"
AND [Type] In (1,4,6)
ORDER BY [Name]

(type 1 means tables in the same database, type 4 means tables linked using
ODBC, type 6 means other linked tables).

The following query will give you the queries:

SELECT [Name]
FROM MSysObjects
WHERE [Type] =5
ORDER BY [Name]

To avoid reinventing the wheel, you might check what Jeff Conrad has at
http://www.accessmvp.com/JConrad/accessjunkie/doctable.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JohnS said:
Is there a way to query Access (from within Access) to get a list of the
tables and queries in the DB?

I am trying to document a DB and would like to list out the table names
and
get counts of tables without counting them by hand and retyping the names
by
hand.

TIA
Click to expand...
Click to expand...
 
Back
Top