How to get table Schema

I am working with Microsoft visual studio 2005.Can anyone tell me how to get table names and column nam开发者_运维百科es of a MS access database?

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

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"

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
rs.Close
Set rs = Nothing

Similarly to adSchemaTables, 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. For example:

Set rs = cn.OpenSchema( _
    adSchemaTables, Array(Empty, Empty, "Employees", "name"))
Debug.Print rs("TABLE_NAME") & "." _ 
    & rs("COLUMN_NAME") & ": " _
    & rs("DATA_TYPE")

For more information see https://msdn.microsoft.com/en-us/library/ms676705.aspx