Query to get all the table names in a database

[blackjack2150]

Hi guys!

What query should I use to retrieve a list of all the table names in an Access database? The effect would be similar to that of a simple "SHOW TABLES;" in MySql. To clear thing up, "SHOW TABLES;" does not work in Access(it says something like: "SELECT, INSERT, UPDATE or DELETE expected").

Thanks in advance.

[markmcgookin]

Hi guys!

What query should I use to retrieve a list of all the table names in an Access database? The effect would be similar to that of a simple "SHOW TABLES;" in MySql. To clear thing up, "SHOW TABLES;" does not work in Access(it says something like: "SELECT, INSERT, UPDATE or DELETE expected").

Thanks in advance.

I don't think this is possible with an SQL query, as they need tables to be pointed to. However, I am sure there would be some simple VB code you could embedd in a form to the same outcome.

[ADezii]

Hi guys!

What query should I use to retrieve a list of all the table names in an Access database? The effect would be similar to that of a simple "SHOW TABLES;" in MySql. To clear thing up, "SHOW TABLES;" does not work in Access(it says something like: "SELECT, INSERT, UPDATE or DELETE expected").

Thanks in advance.

The following SQL will return all 'Non System' Tables:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE MSysObjects.Name Not Like "MsyS*" AND MSysObjects.Type=1
ORDER BY MSysObjects.Name;

[markmcgookin]

The following SQL will return all 'Non System' Tables:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE MSysObjects.Name Not Like "MsyS*" AND MSysObjects.Type=1
ORDER BY MSysObjects.Name;

Well that shut me up! Nice post ADezii

[ADezii]

Well that shut me up! Nice post ADezii

Thanks. It just happened to be one of those 'little things' that I remembered over the years (LOL).

[blackjack2150]

Thanks a lot!
My best regards!