CODEWITHSUNDEEP
sqldatabasems-access
Thunder
Thunder

Reputation: 10986

can we list all tables in msaccess database using sql?

Can we find all tables in the msaccess using sql .

as we do in sqlserver 

select * from sys.tables

in sqlite

SELECT * FROM sqlite_master where type='table'

Upvotes: 20

Views: 57114

Answers (6)

Phillip Hanson
Phillip Hanson

Reputation: 11

For Access 2013, I've used

SELECT name FROM MSysObjects WHERE type = 4

Upvotes: 1

J. Chris Compton
J. Chris Compton

Reputation: 556

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

Upvotes: 3

Parth Gupta
Parth Gupta

Reputation: 251

SELECT name FROM MSysObjects where database <> ''

use this query to get the names of all the linked tables

Upvotes: 0

KTys
KTys

Reputation: 170

This discussion gives a list of Type values. Be aware that MS does not guarantee same values from version to version.

Type    TypeDesc
-32768  Form
-32766  Macro
-32764  Reports
-32761  Module
-32758  Users
-32757  Database Document
-32756  Data Access Pages
1   Table - Local Access Tables
2   Access Object - Database
3   Access Object - Containers
4   Table - Linked ODBC Tables
5   Queries
6   Table - Linked Access Tables
8   SubDataSheets

Upvotes: 0

zendar
zendar

Reputation: 13562

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

Upvotes: 4

Alex K.
Alex K.

Reputation: 175766

Use MSysObjects

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

Upvotes: 21

Related Questions