View RSS Feed

benrwb

Getting a list of tables in an Access database (C#)

Rate this Entry
by , 4th March 2011 at 11:13 AM (8391 Views)
Posting this code so that I can remember it/ and in case anyone else finds it useful.

First open the database
Code:
string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source=database.mdb";
OleDbConnection con = new OleDbConnection(constr);
con.Open();
con.GetSchema() will return a list of collections in the database.

Code:
CollectionName        NumberOfRestrictions NumberOfIdentifierParts
--------------------- -------------------- -----------------------
MetaDataCollections            0                    0
DataSourceInformation          0                    0
DataTypes                      0                    0
Restrictions                   0                    0
ReservedWords                  0                    0
Columns                        4                    4
Indexes                        5                    4
Procedures                     4                    3
Tables                         4                    3
Views                          3                    3
The Tables collection contains the information we want, so call con.GetSchema("Tables").

Code:
TABLE_CATALOG  TABLE_SCHEMA           TABLE_NAME         TABLE_TYPE  TABLE_GUID DESCRIPTION TABLE_PROPID   DATE_CREATED    DATE_MODIFIED
-------------- ------------- --------------------------- ----------- ---------- ----------- ------------ ---------------- ---------------
                             clschk                      TABLE                                           29/12/2010 09:20 29/12/2010 09:20
                             coursedesc                  TABLE                                           29/12/2010 09:20 10/01/2011 09:00
                             MSysAccessStorage           ACCESS TABLE                                    29/12/2010 09:30 29/12/2010 09:30
                             MSysACEs                    SYSTEM TABLE                                    29/12/2010 09:20 29/12/2010 09:20
                             MSysNavPaneGroupCategories  ACCESS TABLE                                    29/12/2010 09:30 29/12/2010 09:30
                             MSysNavPaneGroups           ACCESS TABLE                                    29/12/2010 09:30 29/12/2010 09:30
                             MSysNavPaneGroupToObjects   ACCESS TABLE                                    29/12/2010 09:30 29/12/2010 09:30
                             MSysNavPaneObjectIDs        ACCESS TABLE                                    29/12/2010 09:30 29/12/2010 09:30
                             MSysObjects                 SYSTEM TABLE                                    29/12/2010 09:20 29/12/2010 09:20
                             MSysQueries                 SYSTEM TABLE                                    29/12/2010 09:20 29/12/2010 09:20
                             MSysRelationships           SYSTEM TABLE                                    29/12/2010 09:20 29/12/2010 09:20
                             sessioninfo                 TABLE                                           29/12/2010 09:20 29/12/2010 09:20
                             y11_subjectpred             TABLE                                           29/12/2010 09:20 29/12/2010 11:50
                             y11_tutorltnopro            TABLE                                           29/12/2010 09:20 29/12/2010 09:20
All of the user-created tables have a TABLE_TYPE of "TABLE".

The following code puts them into a list:

Code:
List<string> tables = new List<string>();
foreach (DataRow r in con.GetSchema("Tables").Select("TABLE_TYPE = 'TABLE'"))
    tables.Add(r["TABLE_NAME"].ToString());

Comments

  1. manlypullock's Avatar
    just run this sql "Select DISTINCT(name) FROM sys.Tables" you will get the result

    more info How to find tables in a Database in C# Find the tables in a Database

    Pullock

Trackbacks

Total Trackbacks 0
Trackback URL: