How To Retrieve Column Schema by Using the IDataReader GetSchemaTable Method

The Microsoft KB example code or class library documentation do not explain how to efficiently return a table’s schema. For example, this code follows the documentation:

DataTable dataTable = null;
using(IDbConnection dbConnection = (IDbConnection)database.Unwrap())
{
  dbConnection.Open();
  using (IDbCommand dbCommand = dbConnection.CreateCommand())
  {
    dbCommand.CommandType = CommandType.Text;
    dbCommand.CommandText = "SELECT * FROM SomeLargeTable;";
    using (IDataReader dataReader = 
    dbCommand.ExecuteReader(CommandBehavior.KeyInfo))
    {
      dataTable = dataReader.GetSchemaTable();
      dataReader.Close();
    }
  }
  dbConnection.Close();
}
// look at table schema stored in dataTable

Results in the following SQL executed on Sql Server:

SET FMTONLY OFF; SET NO_BROWSETABLE ON;
SELECT * FROM SomeLargeTable; SET NO_BROWSETABLE OFF;

If you add "| CommandBehavior.SchemaOnly" to the ExecuteReader method call:

SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;
SELECT * FROM SomeLargeTable; SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

In the SqlDataReader implementation, the CommandBehavior.SchemaOnly sets FMTONLY ON which, according to the Transact-SQL Reference, "No rows are processed or sent to the client as a result of the request when SET FMTONLY is turned ON." With (CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly), I had a significant preformance increase - makes perfect sense since returning 100,000,000 rows to retrieve table schema is inefficent, to say the least.

Comments

[Joel Martinez]

yeah, I came across this very issue when I was helping someone write a data migrator that takes one table at a time from one database and moves it to another. Kind of like SQL and Data Compare from red-gate but customized for some app.

If you ask me, that seems like a roundabout way to get something when they should have explicitly included a method to grab the schema without having to do a select statement first (even if the method just did this behind the scenes). Oh well, maybe someday they'll get with the program and pay me truckloads of cash to design their stuff for them ;-)

[credit card machines for small business]

Great perspective on addressing the ability to reduce health care costs without effecting patient care.

[rolex datejust ii]

Nice to come to this website, it let me gain too much.rolex datejust ii