SQL 我们可以使用sql列出msaccess数据库中的所有表吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2629211/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
can we list all tables in msaccess database using sql?
提问by Thunder
Can we find all tables in the msaccess using sql .
我们可以使用 sql 找到 msaccess 中的所有表吗？
as we do in sqlserver
正如我们在 sqlserver 中所做的那样
select * from sys.tables
in sqlite
在 SQLite
SELECT * FROM sqlite_master where type='table'
回答by Alex K.
回答by zendar
Ms Access has several system tables that are, by default, hidden from tables list. You can show them.
Ms Access 有几个系统表，默认情况下，这些表在表列表中是隐藏的。你可以给他们看。
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 Objectscheck 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 Databaseand there is Navigation Optionsbutton.
在 Ms Access 2007 中右键单击表列表并选择Navigation Options。在表格底部，您会找到Show System Objects复选框。检查它，系统表将显示在表列表中。它们都以MSys.
或者，可以从应用程序菜单激活选项表单 - 单击按钮Access options-> 选择Current Database并有Navigation Options按钮。
Now you can examine structure and contents and generate queries of all system tables with MsAccess tools.
现在您可以使用 MsAccess 工具检查结构和内容并生成所有系统表的查询。
As Alex answered, table information is in MSysObjects
正如亚历克斯回答的那样，表格信息在 MSysObjects
回答by J. Chris Compton
The following query helped me scope a redesign/migration from MS Access to C# & SQL Server.
以下查询帮助我重新设计/迁移从 MS Access 到 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)
注意：结合了Alex K.和KTys提供的答案。
在这里发布的信念是它对其他人（或我自己，如果我必须再次这样做）有用
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.
注意来自 KTys 的警告（类型编号可能会发生变化）
添加, *到 select 子句中以查看其他字段（例如连接）；他们对我没有帮助。
Created/tested with MS Access 2013
使用 MS Access 2013 创建/测试
回答by Phillip Hanson
For Access 2013, I've used
对于 Access 2013，我使用过
SELECT name FROM MSysObjects WHERE type = 4
SELECT name FROM MSysObjects WHERE type = 4
回答by KTys
This discussion givesa list of Type values. Be aware that MS does not guarantee same values from version to version.
这个讨论给出了一个类型值的列表。请注意，MS 不保证不同版本的值相同。
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
回答by Parth Gupta
SELECT name FROM MSysObjects where database <> ''
use this query to get the names of all the linked tables
使用此查询获取所有链接表的名称
