How to get table Schema

Question

Hi,

I am working with Microsoft visual studio 2005.Can anyone tell me how to get table names and column names of a MS access database?

Answer 1

Typically, you should the metadata facilities of your database driver to do that. All database call level APIs I have heard of supply methods/functions/procedures that can return database metadata, typically as a resultset.

You didn't mention any language, so lets assume you're using C#. In that case, you'd make a OleDB (OleDB is driver framework for .NET languages) connection object, and invoke the GetOleDbSchemaTable method on it. This will then give you a DataTable object that gives you access to the rows of data that convey this information.

For an OleDB example, see: http://msdn.microsoft.com/en-us/library/aa288452(VS.71).aspx

For the GetOleDbSchemaTable method, see: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.getoledbschematable(VS.71).aspx

For information on the types of metadata provided by OleDB, see: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbschemaguid_members(VS.71).aspx

Answer 2

you can do this by querying the following tables

• MSysAccessObjects

  MSysAccessXML

  MSysACEs

  MSysObjects

  MSysQueries

  MSysRelationships

if these tables are not shown open tables options and on "View" tab check "System Objects"

Answer 3

This example is VBA, but it should be possible to translate the idea as it uses ADO.

  Dim rs As ADODB.Recordset
  Dim cn As ADODB.Connection

      Set cn = "Provider = Microsoft.Jet.OLEDB.4.0; " _
             & "Data Source = MyDB.mdb"

      Set rs = cn.OpenSchema( _
          adSchemaTables, Array(Empty, Empty, Empty))
      Debug.Print rs.GetString
      Set rs = Nothing

Similarly to the above, adSchemaColumns can be used to return columns. The constraints are TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, so you can see that column data for a specific table can be returned, or all columns and the associated table name.

Some more information: http://msdn.microsoft.com/en-us/library/kcax58fh%28VS.80%29.aspx