How to List All Tables in Msaccess Database Using SQL

can we list all tables in msaccess database using sql?

Use MSysObjects

SELECT * FROM MSysObjects WHERE Type=1 AND Flags=0

How can I get table names from an MS Access Database?

To build on Ilya's answer try the following query:

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6)))
order by MSysObjects.Name

(this one works without modification with an MDB)

ACCDB users may need to do something like this

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") 
        AND ((Left([Name],4))<>"MSys") 
        AND ((MSysObjects.Type) In (1,4,6))
        AND ((MSysObjects.Flags)=0))
order by MSysObjects.Name

As there is an extra table is included that appears to be a system table of some sort.

Query to return list of tables in Access

Figured it out on my own actually

SELECT Name
FROM MSysObjects
WHERE (Name NOT LIKE "MSys*") AND (Type=1 OR Type=6);

This one worked for me

How do I retrieve all tables in a database using SQL in Ms Access?

SELECT name
from msysobjects
WHERE type=1 and flags=0
;

Access get all tables

This will bring back all tables in the MS Access database (including linked tables)

SELECT MSysObjects.*, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=1)) OR (((MSysObjects.Type)=6));

It also inclued Sys tables, so you might want to exclude tables starting with MSys

Have a look at

Using MSysObjects
SELECT "Table" AS [Table],
MSysObjects.Name, MSysObjects.

Access SQL (or VBA code) to list all tables with at least one attachment field

Loop the TableDefs collection.

For each TableDef, loop the Fields collection.

For each Field, check the property Type (= 101, as I recall) or IsComplex = True.

IsComplex is also True for Multi-Value fields, but if you don't use these, you should be fine.

How to find list of all tables in Access Database matching certain format in Delphi

A schema may suit:

   Set rs = cn.OpenSchema(adSchemaColumns, _
   Array(Empty, Empty, Empty, SelectFieldName))

Where
adSchemaColumns = 4

rs is a recordset object

cn a connection object

SelectFieldName is the column name, "Date Applied" in this case.

The constraints for this schema are:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME

Columns (fields) returned are:

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
COLUMN_GUID
COLUMN_PROPID
ORDINAL_POSITION
COLUMN_HASDEFAULT
COLUMN_DEFAULT
COLUMN_FLAGS
IS_NULLABLE
DATA_TYPE
TYPE_GUID
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
DESCRIPTION

-- [Obtaining Schema Information from a Database](http://msdn.microsoft.com/en-us/library/kcax58fh(VS.80).aspx)