Click here to Skip to main content
15,867,834 members
Articles / Web Development / ASP.NET
Article

How to get list of tables in a database

Rate me:
Please Sign up or sign in to vote.
3.06/5 (13 votes)
9 Aug 20022 min read 212.6K   2.3K   57   16
A tip on how you can get list of tables in your database

Sample Image - DatabaseTables.jpg

Introduction

Here is a nice little tip on how you can get list of tables in your database. This technique is not just limited to enumeration of tables, it can be used to get a lot of other information about your database. Bur for this article we will limit the discussion to getting list of tables.

Remember from classic ADO Connection.OpenSchema method that was used to get the tables. This functionality did not go away. It is present in OleDb data provider's connection object OleDbConnection. You can use GetOleDbSchemaTable method to get DataTable containing rows corresponding to each table in your database. The first parameter to this method is OleDbSchemaGuid value. This value represents the type of schema that you want to return. For getting list of tables, you will use OleDbSchemaGuid.Tables value. The second parameter to GetOleDbSchemaTable method is an array representing the restrictions that will be applied when returning the values.

OleDbSchemaGuid.Tables value will return all the tables and views in a database. The information returned for this Guid value has 4 restriction columns that you can use to limit the amount of data should be returned from database. Following is the list of restriction columns.

  • TABLE_CATALOG
  • TABLE_SCHEMA
  • TABLE_NAME
  • TABLE_TYPE

If you want to get only the list of tables and not views, system tables etc., then supply the restriction for fourth column with value, TABLE. Following is sample code that we have used in the attached project.

C#
private DataTable GetDatabaseTables_SQL()
{
    string strConn = "Provider=SQLOLEDB;Data Source=localhost;
        Initial Catalog=Northwind;User ID=sa;Password=;";
    OleDbConnection conn = null;
    try
    {
        conn = new OleDbConnection(strConn);
        conn.Open();
        DataTable schemaTable = conn.GetOleDbSchemaTable(
            OleDbSchemaGuid.Tables,
            new object[] {null, null, null, "TABLE"});
        return schemaTable;
    }
    catch (OleDbException ex)
    {
        Trace.Write(ex.Message);
        Response.Write(ex.Message);
    }
    finally
    {
        conn.Close();
    }
    return null;
}                        

We have tested this feature with SQL as well as Oracle database. For SQL database we used the OleDb provider that ships with .NET framework. And for Oracle, we used the OLE DB provider that ships with Oracle 9i database. For more information on how to use Oracle9i OLE DB provider, refer to Use Oracle9i in ASP.Net Application.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United States United States
To learn more about us, Please visit us at http://www.netomatix.com

Comments and Discussions

 
GeneralMy vote of 4 Pin
TaipeiJim24-Oct-12 16:26
TaipeiJim24-Oct-12 16:26 
GeneralMy vote of 4 Pin
D-Kishore4-Sep-12 18:36
D-Kishore4-Sep-12 18:36 
GeneralThen get the tables' names... Pin
DLChambers11-Mar-10 6:44
DLChambers11-Mar-10 6:44 
GeneralSqlWhereBuilder Pin
K.A.Firdous Ali30-Mar-06 2:40
K.A.Firdous Ali30-Mar-06 2:40 
GeneralSqlWhereBuilder for Oracle9i Pin
K.A.Firdous Ali29-Mar-06 23:13
K.A.Firdous Ali29-Mar-06 23:13 
GeneralGetOleDBSchemaTable Pin
Barcoder24-Oct-03 8:45
Barcoder24-Oct-03 8:45 
GeneralAnother Way... Pin
Tim McCurdy21-Aug-03 16:54
Tim McCurdy21-Aug-03 16:54 
Generalgreat, thanks! Pin
Ashley van Gerven24-Jul-03 15:21
Ashley van Gerven24-Jul-03 15:21 
QuestionUm.....Why? Pin
Mark Conger15-Nov-02 3:16
Mark Conger15-Nov-02 3:16 
AnswerRe: Um.....Why? Pin
Ashley van Gerven24-Jul-03 15:23
Ashley van Gerven24-Jul-03 15:23 
GeneralRe: Um.....Why? Pin
Mark Conger26-Jul-03 1:09
Mark Conger26-Jul-03 1:09 
GeneralRe: Um.....Why? Pin
Jerry III22-Feb-04 11:18
Jerry III22-Feb-04 11:18 
GeneralRe: Um.....Why? Pin
Neeraj Saluja29-Jun-04 0:08
Neeraj Saluja29-Jun-04 0:08 
GeneralStrored Procedure Parameter Pin
cwilke15-Nov-02 1:12
cwilke15-Nov-02 1:12 
GeneralRe: Strored Procedure Parameter Pin
Mark Conger15-Nov-02 3:07
Mark Conger15-Nov-02 3:07 
GeneralSolution found! Pin
cwilke17-Nov-02 21:29
cwilke17-Nov-02 21:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.