Query table information with T-SQL

[DanRobertson]

I have been looking all over for the answer to this and can't find the slightest thing about it.

I need to know how I can query information about what tables are in a MS Access database. Lets say for example I can't access the database with the MS Access application and I want to write a web query in asp to list the tables in the database. For example, I may not know what tables are in there and need to find out.

I know there is a way with mySQL using the "show tables" command but it doen't appear to work with Access

I also came accross some code with OpenSchema(adSchemaTables) that is suppose to do the trick, but it either doesn't work or my shared-hosting provider doen't have it set up to work.

If anyone could PLEASE help me out with the T-SQL query code to get table info I would be greatly thankful!

Thanks in advance,
Dan
dan@innovativead.com

 

[Pat Hartman]

Each Access database contains a table named MSysObjects which can be used if necessary to obtain this information. Try this query:

SELECT msysobjects.Name, msysobjects.Connect, msysobjects.Database, msysobjects.Type
FROM msysobjects
WHERE msysobjects.Type In (1,4,6) AND Left(msysobjects.name, 4) <> "MSys";

The recommended solution is to examine the tabledefs collection because Microsoft does not promise that the MSys tables will remain constant from version to version.