can we list all tables in msaccess database using sql?
Ms Access has several system tables that are, by default, hidden from tables list. You can show them.
In Ms Access 2007 do a right click on tables list and select Navigation Options. At the bottom of the form you will find Show System Objects check box. Check it and system tables will show up in tables list. They all start with MSys.
Alternatively, options form can be activated from application menu - click button Access options -> select Current Database and there is Navigation Options button.
Now you can examine structure and contents and generate queries of all system tables with MsAccess tools.
As Alex answered, table information is in MSysObjects
The following query helped me scope a redesign/migration from MS Access to C# & SQL Server.
Note: Combines answers provided by both Alex K. and KTys.
Posted here with the belief that it will be useful to someone else (or myself if I have to do this again)
SELECT SWITCH ( [type]=-32764,'Report' , [type] = 1, 'Table, local' , [type] = 3, 'obj Containers' , [type] = 4, 'Table, link odbc' , [type] = 5, 'Query' , [type] = 6, 'Table, link access' , [type] = 8, 'SubDataSheets' , TRUE, [type] ) AS [type name (or #)] , name AS [Table Name]FROM MSysObjects ORDER BY 2, 3
Note warning from KTys (type numbers are subject to change)
Add , * to the select clause to see the other fields (such as connect); they weren't helpful to me.
Created/tested with MS Access 2013