获取ACCESS数据库中所有表及对象信息

1.利用OleDbConnection 对象的 GetOleDbSchemaTable 方法

OLE DB .NET 数据提供程序使用 OleDbConnection 对象的 GetOleDbSchemaTable 方法展示架构信息。GetOleDbSchemaTable 返回填充了架构信息的 DataTable。

GetOleDbSchemaTable 的第一个参数是架构参数，它是一个 OleDbSchemaGuid 类型的标识，指定了要返回的架构信息的类型（如表、列和主键）。第二个参数是一个限制对象数组，对 DataTable 架构中返回的行进行过滤（例如，您可以指定对表的名称、类型、所有者和/或架构的限制）。

Dim myConn As New OleDb.OleDbConnection

Dim myTable As DataTable

myConn.ConnectionString = connectionString

myConn.Open()

‘获取数据库中所有表（包含系统表、视图）

myTable = myConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)

‘得到全部的用户表,用户表类型为Table，进行过虑

myTable = myConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

‘获取全部的用户视图，类型为VIEW

myTable = myConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "VIEW"})

‘获取全部的存储过程

myTable = myConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Procedures, Nothing)

‘获取所有支持的数据类型

myTable = myConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Provider_Types, Nothing)

‘获取每个表的主键

myTable = myConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Primary_Keys, Nothing)

2.利用ACCESS系统表获取对象信息

查询:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=5 ORDER BY MSysObjects.Name;

窗体:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)=-32768 ORDER BY MSysObjects.Name;

表:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;

报表:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;

模块:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;

宏:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;