Getting Schema Information from your Database

by William Ryan

There may be times when you'll want to interrogate your database and find information about it's schema like table names, table definitions etc.  ADO.NET gives us two very cool ways to accomplish this.  Most of the time, when you call one of SqlClient's methods or properties, you can do the same with OleDb, Odbc etc.  Most of the time, not always.  In this instance however, the approach is fundamentally different.  If you are using SqlClient, you simply query Sql Server's Information_Schema and restrict the results so that Table_Type = BASE TABLE.  On the other hand, with OleDb (Access comes to mind on this) you open a connection, and then call its OleDbConnection.GetOleDbSchematable method.

So, assuming that you are using SqlClient, this is basically how you do it.

VB.NET

Dim cn as New SqlConnection("YourConnectionStringHere") Dim da as New SqlDataAdapter("SELECT * FROM Information_Schema.Tables where Table_Type = 'BASE TABLE'", cn) Dim dt as New DataTable da.Fill(dt)

C#

SqlConnection cn = new SqlConnection("YourConnectionStringHere"); SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Information_Schema.Tables where Table_Type = 'BASE TABLE'", cn"); DataTable dt = new DataTable(); da.Fill(dt);

However, if we do it with OleDb, we don't need a DataAdapter, we're going to let the connection get the information for us:

VB.NET

Dim cn as New OleDbConnection("YourConnectionStringHere") Dim dt as New DataTable cn.Open dt  = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {nothing, nothing, nothing, "Table"}) cn.Close

C#

OleDbConnection cn = new OleDbConnection("YourConnectionStringHere"); cn.Open(); DataTable dt = new DataTable(); dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] {null, null, null, "Table"}); cn.Close

As you can see, there's not much to it.  Now, you're probably wondering what the whole New Object thing is about.  That is an array that you can pass in to restrict what comes back.  So, if you passed in values for the first element instead of nothing, only values that matched that value would be returned.  I don't use Access enough to really know what those other values are and I haven't been able to find out what other values you could pass in for "Table".  I'm guessing it will probably be indexes or views but that's purely conjecture on my part.  Regardless, if you want all of the information on all of the tables, the above code snippets will do it for you.  Also, remember that since we aren't using a DataAdapter object in the second part, we need to explicitly open and close the connections in order for it to work (It'd probably be pretty hard for a connection to return information about a database that it's never opened up to).

See Also, OleDbConnection.GetOleDbSchemaTable | OleDbSchemaGuid | System.Data.OleDb | OleDbConnection Members