Click here to Skip to main content
Click here to Skip to main content

How to get list of tables in a database

, 9 Aug 2002
Rate this:
Please Sign up or sign in to vote.
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.

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

Share

About the Author

Softomatix
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 PinmemberTaipeiJim24-Oct-12 17:26 
GeneralMy vote of 4 PinmemberD-Kishore4-Sep-12 19:36 
GeneralThen get the tables' names... PinmemberDLChambers11-Mar-10 7:44 
GeneralSqlWhereBuilder PinmemberK.A.Firdous Ali30-Mar-06 3:40 
GeneralSqlWhereBuilder for Oracle9i PinmemberK.A.Firdous Ali30-Mar-06 0:13 
GeneralGetOleDBSchemaTable PinmemberBarcoder24-Oct-03 9:45 
GeneralAnother Way... PinmemberTim McCurdy21-Aug-03 17:54 
Generalgreat, thanks! Pinmemberashley_vg24-Jul-03 16:21 
QuestionUm.....Why? PinmemberMark Conger15-Nov-02 4:16 
AnswerRe: Um.....Why? Pinmemberashley_vg24-Jul-03 16:23 
GeneralRe: Um.....Why? PinmemberMark Conger26-Jul-03 2:09 
GeneralRe: Um.....Why? PinmemberJerry III22-Feb-04 12:18 
GeneralRe: Um.....Why? PinmemberNeeraj_Saluja29-Jun-04 1:08 
GeneralStrored Procedure Parameter PinsussCW15-Nov-02 2:12 
GeneralRe: Strored Procedure Parameter PinmemberMark Conger15-Nov-02 4:07 
GeneralSolution found! PinsussCW17-Nov-02 22:29 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150123.1 | Last Updated 10 Aug 2002
Article Copyright 2002 by Softomatix
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid